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,2),[time] date)
insert into tableA
select 'Tommy',100,'2012-1-10'
union all select 'Tommy',101,'2012-2-11'
union all select 'Tommy',102,'2012-8-12'
union all select 'Tommy',103,'2012-4-13'
union all select 'Tommy',104,'2012-10-14'
union all select 'Jenny',105,'2012-6-15'
union all select 'Jenny',106,'2012-3-16'
union all select 'Jenny',107,'2012-8-17'
union all select 'Jenny',108,'2012-9-18'
union all select 'Jenny',109,'2012-5-19'
union all select 'Jenny',110,'2012-11-20'
union all select 'Jenny',111,'2012-12-21'
declare @groupField varchar(1000)
select @groupField=isnull(@groupField,'')+case when isnull(@groupField,'')='' then '' else ',' end+QUOTENAME(YM)
from (select distinct CONVERT(varchar(7),[time],120) as YM from tableA)t
declare @sql nvarchar(4000)
set @sql=N'select *
from
(select Name, CONVERT(varchar(7),[time],120) YM,sum(Amount) as Amount
from tableA
group by Name,CONVERT(varchar(7),[time],120)
) as x
pivot (sum(Amount)for YM in ('+@groupField+')) as pvt
order by Name'
EXEC (@sql)
drop table tableA
/*
Name2012-012012-022012-032012-042012-052012-062012-082012-092012-102012-112012-12
---------------------------------------------------------------
JennyNULLNULL106.00NULL109.00105.00107.00108.00NULL110.00111.00
Tommy100.00101.00NULL103.00NULLNULL102.00NULL104.00NULLNULL
*/
