日期:2014-05-17 浏览次数:20394 次
Create table tbdata (TLname nvarchar(8),TLdate datetime,TLsum decimal)
Insert tbdata
select '第一名','2012-01-01',100 union all
select '第一名','2012-02-01',600 union all
select '第二名','2012-03-01',500 union all
select '第二名','2012-03-01',400 union all
select '第三名','2012-04-01',200 union all
select '第四名','2012-05-01',300
select TLname,sum(TLsum) as 数量,month(TLdate) as 月份 from tbdata group by TLname,month(TLdate) order by TLname
TLname 数量 月份
-------- ------------ -----------
第一名 100 1
第一名 600 2
第三名 200 4
第二名 900 3
第四名 300 5
(5 行受影响)
Create table tbdata (TLname nvarchar(8),TLdate datetime,TLsum decimal)
Insert tbdata
select '张三','2012-01-01',100 union all
select '李四','2012-02-01',600 union all
select '张三','2012-03-01',500 union all
select '李四','2012-03-01',400 union all
select '李四','2012-04-01',200 union all
select '张三','2012-05-01',300 union all
select '张三','2013-01-01',100 union all
select '李四','2013-02-01',200 union all
select '张三','2013-03-01',200 union all
select '李四','2013-03-01',700 union all
select '李四','2013-04-01',900 union all
select '张三','2013-05-01',300
select year(tldate) as 年份,TLname
,[1]=sum(case when month(tldate)=1 then tlsum else 0 end)
,[2]=sum(case when month(tldate)=2 then tlsum else 0 end)
,[3]=sum(case when month(tldate)=3 then tlsum else 0 end)
,[4]=sum(case when month(tldate)=4 then tlsum else 0 end)
,[5]=sum(case when month(tldate)=5 then tlsum else 0 end)
,[6]=sum(case when month(tldate)=6 then tlsum else 0 end)