求SQL语句谢谢！！
表TT如下：
idnoBicdate ictime
00000812007/9/26 01:00:00
00000812007/9/26 07:00:00
00000812007/9/26 12:30:00
00000812007/9/26 13:30:00
00000812007/9/26 17:00:00
00000812007/9/27 00:30:00
00001122007/9/26 13:00:00
00001122007/9/26 19:00:00
00001122007/9/26 23:00:00
00001122007/9/27 01:00:00
00001122007/9/27 07:00:00
00001122007/9/27 11:30:00
00001122007/9/27 12:30:00
要得 到查询结果
idnob icdate T1 T2 T3 T4 T5 T6
0000081 2007/9/26 01:00:00 07:00:00 12:30:00 13:30:00 17:00:00 00:30:00
T7 T8
null null
0000112 2007/9/26 13:00:00 19:00:00 23:00:00 01:00:00 07:00:00 11:30:00
T7 T8
12:30:00 null
注：
B 为1 的 第一天13:00~ 第二天12:30
B 为2 的 第一天1:00 ~ 第二天00:30
查询分析出如上 T1~T8 个时间段先后也如上。超出8个的可以不用理会。注意icdate 的日期。
先谢谢各位了！！
[解决办法]
declare @sql varchar(8000)
set @sql = 'select idbo,Sdate '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then stime else ' ' ' ' end) [T ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime <t.stime)+1 , * from tb t) m where px <= 8) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime <t.stime)+1 , * from tb t) m where px <= 8 group by idbo,Sdate '
exec(@sql)
[解决办法]
--用你昨天的数据测试 <=4的情况, <=8的情况类似,把数据改了就行了.
create table tb(idbo varchar(10),dd int,Sdate varchar(10),stime varchar(10))
insert into tb values( '000008 ',1, '2007/09/26 ', '08:00:00 ')
insert into tb values( '000008 ',1, '2007/09/26 ', '12:30:00 ')
insert into tb values( '000008 ',1, '2007/09/26 ', '13:30:00 ')
insert into tb values( '000008 ',1, '2007/09/26 ', '17:00:00 ')
insert into tb values( '000011 ',1, '2007/09/26 ', '07:30:00 ')
insert into tb values( '000011 ',1, '2007/09/26 ', '11:30:00 ')
insert into tb values( '000011 ',1, '2007/09/26 ', '11:45:00 ')
insert into tb values( '000011 ',1, '2007/09/26 ', '16:30:00 ')
insert into tb values( '000011 ',1, '2007/09/26 ', '16:45:00 ')
insert into tb values( '000011 ',1, '2007/09/26 ', '21:00:00 ')
insert into tb values( '000008 ',1, '2007/09/27 ', '08:20:07 ')
insert into tb values( '000008 ',1, '2007/09/27 ', '12:20:00 ')
insert into tb values( '000008 ',1, '2007/09/27 ', '13:00:00 ')
insert into tb values( '000008 ',1, '2007/09/27 ', '17:30:00 ')
go
declare @sql varchar(8000)
set @sql = 'select idbo,Sdate '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then stime else ' ' ' ' end) [T ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime <t.stime)+1 , * from tb t) m where px <= 4) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime <t.stime)+1 , * from tb t) m where px <= 4 group by idbo,Sdate '
exec(@sql)
/*
idbo Sdate T1 T2 T3 T4
---------- ---------- ---------- ---------- ---------- ----------
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
*/
drop table tb
[解决办法]
这下我可看不懂了?
[解决办法]
--原始数据：#A
create table #A(idno varchar(6),B int,icdate varchar(10),ictime varchar(8))
insert #A
select '000008 ',1, '2007/9/26 ', '01:00:00 ' union all
select '000008 ',1, '2007/9/26 ', '07:00:00 ' union all
select '000008 ',1, '2007/9/26 ', '12:30:00 ' union all
select '000008 ',1, '2007/9/26 ', '13:30:00 ' union all
select '000008 ',1, '2007/9/26 ', '17:00:00 ' union all
select '000008 ',1, '2007/9/27 ', '00:30:00 ' union all
select '000011 ',2, '2007/9/26 ', '13:00:00 ' union all
select '000011 ',2, '2007/9/26 ', '19:00:00 ' union all
select '000011 ',2, '2007/9/26 ', '23:00:00 ' union all
select '000011 ',2, '2007/9/27 ', '01:00:00 ' union all
select '000011 ',2, '2007/9/27 ', '07:00:00 ' union all
select '000011 ',2, '2007/9/27 ', '11:30:00 ' union all
select '000011 ',2, '2007/9/27 ', '12:30:00 '
declare @Max int,@T varchar(10),@sql varchar(8000)
set @T=1
while @T <=8
select @sql=coalesce(@sql+ ', ', 'select idno,min(icdate), ')+ '[T '+@T+ ']=max(case CT when '+@T+ ' then ictime end) ',@T=@T+1
set @sql = @sql+ ' from (select CT=(select count(1) from #A where idno=a.idno and B=a.B and icdate+ictime <=a.icdate+a.ictime), * from #A a) a group by idno,B '
exec (@sql)
/*
odno icdate T1 T2 T3 T4 T5 T6 T7 T8
000008 2007/9/26 01:00:00 07:00:00 12:30:00 13:30:00 17:00:00 00:30:00 NULL NULL
000011 2007/9/26 13:00:00 19:00:00 23:00:00 01:00:00 07:00:00 11:30:00 12:30:00 NULL
*/
--删除对象
drop table #A
------解决方案--------------------
说白了就是根据 B 分组，icdate 取最小，其它照旧。
