＋设为首页 ＋添加到收藏夹
原始表如下格式：
Class CallDate CallCount
1 2005-8-8 40
1 2005-8-7 6
2 2005-8-8 77
3 2005-8-9 33
3 2005-8-8 9
3 2005-8-7 21
根据Class的值，按日期分别统计出CallCount1，CallCount2，CallCount3。
当该日期无记录时值为0
要求合并成如下格式：
CallDate CallCount1 CallCount2 CallCount3
2005-8-9 0 0 33
2005-8-8 40 77 9
2005-8-7 6 0 21
select CallDate,
CallCount1=sum(case when class=1 then Callcount else 0 end),
CallCount2=sum(case when class=2 then Callcount else 0 end),
CallCount3=sum(case when class=3 then Callcount else 0 end)
from tblname
group by CallDate
declare @tab table(Class int,CallDate datetime,CallCount int)
insert @tab
values(1,2005-8-8,40)
insert @tab
values(1,2005-8-7,6)
insert @tab
values(2,2005-8-8,77)
insert @tab
values(3,2005-8-9,33)
insert @tab
values(3,2005-8-8,9)
insert @tab
values(3,2005-8-7,21)
select * from @tab
select CallDate,
CallCount1=max(case Class when 1 then CallCount else 0 end),
CallCount2=max(case Class when 2 then CallCount else 0 end),
CallCount3=max(case Class when 3 then CallCount else 0 end)
from @tab
group by CallDate
order by CallDate desc
想用一条语句查询比较困难,如果用存储过程就可以!
--创建测试环境
Create table T (Class varchar(2),CallDate datetime, CallCount int)
insert into T select 1,2005-8-8,40
union all select 1,2005-8-7,6
union all select 2,2005-8-8,77
union all select 3,2005-8-9,33
union all select 3,2005-8-8,9
union all select 3,2005-8-7,21
--动态SQL
declare @s varchar(8000)
set @s=select CallDate
select @s=@s+,[CallCount+Class+]=sum(case when Class=+Class+ then CallCount else 0 end)
from T
group by Class
set @s=@s+ from T group by CallDate order by CallDate desc
exec(@s)
--结果
CallDate CallCount1 CallCount2 CallCount3
------------------------------------------------------ ----------- ----------- -----------
2005-08-09 00:00:00.000 0 0 33
2005-08-08 00:00:00.000 40 77 9
2005-08-07 00:00:00.000 6 0 21
--删除环境
drop table T
declare @sql varchar(8000)
set @sql =
select @sql = @sql + ,sum(case when class= + cast(class as varchar(10)) + then callcount else 0 end ) as callcount + cast(class as varchar(10)) from [表] group by class order by class
exec ( select calldate + @sql + from [表] group by calldate )
declare @sql varchar(8000)
set @sql =
select @sql = @sql + ,callcount+rtrim(class)+=sum(case class when + rtrim(class) + then callcount else 0 end ) from [表] group by class order by class
exec ( select calldate + @sql + from [表] group by calldate)
select a.calldate, b.CallCount , c.CallCount, d.CallCount , csum
from
(select calldate from T group by calldate ) a
inner join T b on a.calldate = b.calldate and b.class =1
inner join T c on a.calldate = c.calldate and c.class =2
inner join T d on a.calldate = d.calldate and d.class =3
inner join (select a.calldate , sum(callcount) as csum from T group by calldate)
e on a.calldate = e.calldate
这样应该可以的,没测试哦,我这里没环境
