Sql 按月份分组
Table A(Name,Amount,Time)
想要的结果
Name,Mon,Feb....
其中,Mon/Feb/...显示按照月份的Sum(Amount)
求大牛指导。
sql
分享到:
------解决方案--------------------
select name,[mon]=sum(case when month(time)=1 then amount else 0 end ),
[feb]=sum(case when month(time)=2 then amount else 0 end )..12个月自己补全
from a
group by name
------解决方案--------------------
--如果不是2008以上版本,用这个
select name,[mon]=sum(case when CONVERT(INT,SUBSTRING(CONVERT(VARCHAR(30),[time],23),6,2))=1 then amount else 0 end ),
[feb]=sum(case when CONVERT(INT,SUBSTRING(CONVERT(VARCHAR(30),[time],23),6,2))=2 then amount else 0 end )..12个月自己补全
from a
group by name
------解决方案--------------------
create table tableA(Name varchar(10),Amount numeric(12,