table A:
id , date , quantity
1 , 2004/1/20 100
2 , 2004/2/20 100
3 , 2004/3/20 100
4 , 2004/3/22 100
5 , 2004/5/20 100
6 , 2004/5/30 100
7 , 2004/7/20 100
我想得到的数据格式如下:
即根据2004年的月份统计数量
月份 总量
1 100
2 100
3 200
4 0
5 200
6 0
7 100
8
........
CREATE TABLE [dbo].[A](
[Id] [int] NOT NULL,
[date] [datetime] NULL,
[quantity] [int] NULL
) ON [PRIMARY]
insert into a(Id,date,quantity) values( 1 , '2004-01-20 00:00:00.000' , 100 )
insert into a(Id,date,quantity) values( 2 , '2004-02-20 00:00:00.000' , 100 )
insert into a(Id,date,quantity) values( 3 , '2004-03-20 00:00:00.000' , 100 )
insert into a(Id,date,quantity) values( 4 , '2004-03-22 00:00:00.000' , 100 )
insert into a(Id,date,quantity) values( 5 , '2004-05-20 00:00:00.000' , 100 )
insert into a(Id,date,quantity) values( 6 , '2004-05-30 00:00:00.000' , 100 )
insert into a(Id,date,quantity) values( 7 , '2004-07-20 00:00:00.000' , 100 )
select 年份=year(date)
,[1]=sum(case month(date) when 1 then quantity else 0 end)
,[2]=sum(case month(date) when 2 then quantity else 0 end)
,[3]=sum(case month(date) when 3 then quantity else 0 end)
,[4]=sum(case month(date) when 4 then quantity else 0 end)
,[5]=sum(case month(date) when 5 then quantity else 0 end)
,[6]=sum(case month(date) when 6 then quantity else 0 end)
,[7]=sum(case month(date) when 7 then quantity else 0 end)
,[8]=sum(case month(date) when 8 then quantity else 0 end)
,[9]=sum(case month(date) when 9 then quantity else 0 end)
,[10]=sum(case month(date) when 10 then quantity else 0 end)
,[11]=sum(case month(date) when 11 then quantity else 0 end)
,[12]=sum(case month(date) when 12 then quantity else 0 end)
from A
group by year(date)
