当前位置:Gxlcms > PHP教程 > 经常用到的交叉表问题,一般用动态SQL能生成动态列!_PHP教程

经常用到的交叉表问题,一般用动态SQL能生成动态列!_PHP教程

时间:2021-07-01 10:21:17 帮助过:19人阅读

原始表如下格式:
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
--创建测试环境
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


www.bkjia.comtruehttp://www.bkjia.com/PHPjc/631231.htmlTechArticle原始表如下格式: 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的值,按日期分别统计出CallC...

人气教程排行