如何用指定时间段为动态字段生产交叉报表

[hpy121]

数据库中表的存储格式如下
考点 考场 参考人数 开考时间年 月 日 时 分
01 01 50 2005 5 20 8 30
01 02 51 2005 5 21 12 30
02 01 52 2005 5 20 15 30
02 01 53 2005 5 22 19 30

每天有四场考试分别为上午：8：30－11：30 ；中午12：30 － 14：30 ；
下午：15：30 － 17 ：30 ；晚上19：30 － 21 ：30
要求：输出指定时间段内各考点每天四场考试的参考人数
如上表中指定时间段2005年5月20到2005年5月22日，则输出表

2005－5－20 2005－5－21 2005－5－22
考点 上午 中午 下午 晚上 上午 中午 下午 晚上 上午 中午 下午 晚上
01 50 0 0 0 0 51 0 0 0 0 0 0
02 0 0 52 0 0 0 0 0 0 0 0 53

[hpy121]

呵呵，谢谢大家，特别是libin_ftsafe(子陌红尘)

[子陌红尘]

create table #t(
考点 varchar(10),
考场 varchar(10),
参考人数 int,
开考时间年 varchar(4),
月 varchar(2),
日 varchar(2),
时 varchar(2),
分 varchar(2))
insert into #t select '01','01',50,'2005','5','20','8' ,'30'
insert into #t select '01','02',51,'2005','5','21','12','30'
insert into #t select '02','01',52,'2005','5','20','15','30'
insert into #t select '02','01',53,'2005','5','22','19','30'


declare @s varchar(8000)
set @s = ''

select @s = @s + ',['+开考时间年+'-'+月+'-'+日+'上午]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''08:30'' and ''11:30'' then 参考人数 else 0 end)'
+ ',['+开考时间年+'-'+月+'-'+日+'中午]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''12:30'' and ''14:30'' then 参考人数 else 0 end)'
+ ',['+开考时间年+'-'+月+'-'+日+'下午]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''15:30'' and ''17:30'' then 参考人数 else 0 end)'
+ ',['+开考时间年+'-'+月+'-'+日+'晚上]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''19:30'' and ''21:30'' then 参考人数 else 0 end)'
from
(select distinct 开考时间年,月,日 from #t) t


set @s = 'select 考点'+@s+' from #t group by 考点'

exec(@s)


--结果自己看

[子陌红尘]

注意：如果--开考时间年、月、日、时、分以数字类型字段保存，则需要在引用时转为varchar()类型

[vivianfdlpw]

红尘好厉害~

[hpy121]

呵呵，结果好像还是不对，SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''08:30'' and ''11:30'' then 参考人数 else 0 end)会把表中所有时间段在8：30到11：30的参赛考人数都统计进来
以上得到的结果如下：
01 50 51 0 0 50 51 0 0 50 51 0 0
02 0 0 52 53 0 0 52 53 0 0 52 53

显然不对

[子陌红尘]

是有问题，忘记把年月日的条件加上了，再试：
--------------------------------------------------------------------------
create table #t(
考点 varchar(10),
考场 varchar(10),
参考人数 int,
开考时间年 varchar(4),
月 varchar(2),
日 varchar(2),
时 varchar(2),
分 varchar(2))
insert into #t select '01','01',50,'2005','5','20','8' ,'30'
insert into #t select '01','02',51,'2005','5','21','12','30'
insert into #t select '02','01',52,'2005','5','20','15','30'
insert into #t select '02','01',53,'2005','5','22','19','30'



declare @s varchar(8000)
set @s = ''

select @s = @s + ',['+开考时间年+'-'+月+'-'+日+'上午]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''08:30'' and ''11:30'' then 参考人数 else 0 end)'
+ ',['+开考时间年+'-'+月+'-'+日+'中午]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''12:30'' and ''14:30'' then 参考人数 else 0 end)'
+ ',['+开考时间年+'-'+月+'-'+日+'下午]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''15:30'' and ''17:30'' then 参考人数 else 0 end)'
+ ',['+开考时间年+'-'+月+'-'+日+'晚上]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''19:30'' and ''21:30'' then 参考人数 else 0 end)'
from
(select distinct 开考时间年,月,日 from #t) t


set @s = 'select 考点'+@s+' from #t group by 考点'
print @s
exec(@s)

[hulijn]

他只考虑了时间，没考虑日期，把日期加进去一起考虑，应该就可以了吧

[hulijn]

libin_ftsafe(子陌红尘)
速度迅速啊

[hulijn]

我字还没打完，都改好啦
佩服！佩服！

[paoluo]

--建立测试环境
Create Table TEST(
考点 varchar(10),
考场 varchar(10),
参考人数 int,
开考时间年 varchar(4),
月 varchar(2),
日 varchar(2),
时 varchar(2),
分 varchar(2))
--插入数据
Insert Into TEST Select '01','01',50,'2005','5','20','8' ,'30'
Insert Into TEST Select '01','02',51,'2005','5','21','12','30'
Insert Into TEST Select '02','01',52,'2005','5','20','15','30'
Insert Into TEST Select '02','01',53,'2005','5','22','19','30'
GO
--测试
Declare @S Nvarchar(4000)
Set @S=N'Select 考点'
Select @S=@S+N',SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''8'' And 时<''12'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'上午],
SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''12'' And 时<''15'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'中午],
SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''15'' And 时<''19'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'下午],
SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''19'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'晚上]'
from (Select Distinct 开考时间年,月,日 from TEST ) A
Select @S=@S+ N' from TEST Group By 考点 '
--Select @S
EXEC(@S)
--删除测试环境
Drop Table TEST
--结果
/*
考点 2005-5-20上午 2005-5-20中午 2005-5-20下午 2005-5-20晚上 2005-5-21上午 2005-5-21中午 2005-5-21下午 2005-5-21晚上 2005-5-22上午 2005-5-22中午 2005-5-22下午 2005-5-22晚上
01 0 0 0 50 0 51 0 0 0 0 0 0
02 0 0 52 0 0 0 0 0 0 0 0 53
*/