--生成测试数据
create table test(id int,[Day] int,[Time] varchar(10))
insert into test select 1,9 ,'16:30'
insert into test select 1,9 ,'16:38'
insert into test select 3,9 ,'16:30'
insert into test select 3,9 ,'16:53'
insert into test select 3,9 ,'17:31'
insert into test select 3,10,'08:01'--执行查询过程
select
a.*,
colid=(select count(*) from test where id=a.id and [Day]=a.[Day] and [Time]<=a.[Time])
into #T
from
test adeclare @s varchar(8000),@i int
select @i = max(colid) from #t
set @s = ''while @i>0
begin
set @s = ',[Time'+rtrim(@i)+']=max(case colid when '+rtrim(@i)+' then [Time] end)'+@s
set @i = @i - 1
endset @s = 'select ID,[Day]'+@s+' from #t group by ID,[Day] order by ID,[Day]'
exec(@s)--删除测试数据
drop table test,#T
--输出结果
/*
1 9 16:30 16:38 NULL
3 9 16:30 16:53 17:31
3 10 08:01 NULL NULL
*/
create table test(id int,[Day] int,[Time] varchar(10))
insert into test select 1,9 ,'16:30'
insert into test select 1,9 ,'16:38'
insert into test select 3,9 ,'16:30'
insert into test select 3,9 ,'16:53'
insert into test select 3,9 ,'17:31'
insert into test select 3,10,'08:01'--执行查询过程
select
a.*,
colid=(select count(*) from test where id=a.id and [Day]=a.[Day] and [Time]<=a.[Time])
into #T
from
test adeclare @s varchar(8000),@i int
select @i = max(colid) from #t
set @s = ''while @i>0
begin
set @s = ',[Time'+rtrim(@i)+']=max(case colid when '+rtrim(@i)+' then [Time] end)'+@s
set @i = @i - 1
endset @s = 'select ID,[Day]'+@s+' from #t group by ID,[Day] order by ID,[Day]'
exec(@s)--删除测试数据
drop table test,#T
--输出结果
/*
1 9 16:30 16:38 NULL
3 9 16:30 16:53 17:31
3 10 08:01 NULL NULL
*/
colid=(select count(*) from test where id=a.id and [Day]=a.[Day] and [Time]<=a.[Time])
还有这个条件的作用是什么?
[Time]<=a.[Time])
还有这句不是很明白。
set @s = ',[Time'+rtrim(@i)+']=max(case colid when '+rtrim(@i)+' then [Time] end)'+@s
谢谢。
用于计算表中当前记录有多少条时间小于等于当前记录的记录,用于指明最后要生成多少个Timei,所以@i取最大的值,set @s = ',[Time'+rtrim(@i)+']=max(case colid when '+rtrim(@i)+' then [Time] end)'+@s
这里时生成 Time1,Time2,Time3,.... TimeN