MyException - 我的异常网
当前位置:我的异常网» Sql Server » 求教~分别(汇总)查询12个月的数据 (改过),该如

求教~分别(汇总)查询12个月的数据 (改过),该如何处理(2)

www.MyException.Cn  网友分享于:2013-12-28  浏览:223次

[10]=sum(case datepart(mm,times) when 10 then charge else 0 end),
[11]=sum(case datepart(mm,times) when 11 then charge else 0 end),
[12]=sum(case datepart(mm,times) when 12 then charge else 0 end)
from
T1 a,T2 b
where
a.peopleID=b.peopleID
group by
a.name
------解决方案--------------------
create table t1(peopleID int, times datetime , nameID int , name varchar(50))
insert t1 select 1, '2006-01-12 ', 1, '张三 '
union all select 2, '2006-01-11 ', 1, '张三 '
union all select 3, '2006-01-02 ', 2, '李四 '
union all select 4, '2006-02-02 ', 2, '李四 '


create table t2(listID int , peopelID int , charge decimal)
insert t2 select 10, 1, 50
union all select 11, 2, 100
union all select 12, 3, 500
union all select 13, 4, 600

--如果只是1年的数据:
declare @sql varchar(2000)
set @sql= ' '
select @sql=@sql+ ',[ '+rtrim(times)+ '月]=sum(case month(times) when '+rtrim(times)+ ' then charge else 0 end) '
from (select month(times)times from T1 group by month(times))ta
set @sql= 'select name '+@sql+ ' from t1 join t2 on t1.peopleID=t2.peopelID group by name '
print @sql
exec(@sql)
name 1月 2月
-------------------------------------------------- ---------------------------------------- ----------------------------------------
李四 500 600
张三 150 0

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有