求高手赐SQL语句！！！！！！！！！！！！！
create table #A(idno varchar(6),B varchar(1),icdate varchar(10),ictime varchar(8))
insert #A
select '000008 ',T, '2007/9/26 ', '01:00:00 ' union all
select '000008 ',T, '2007/9/26 ', '07:00:00 ' union all
select '000008 ',T, '2007/9/26 ', '12:30:00 ' union all
select '000008 ',T, '2007/9/26 ', '13:30:00 ' union all
select '000008 ',T, '2007/9/26 ', '17:00:00 ' union all
select '000008 ',T, '2007/9/27 ', '00:30:00 ' union all
select '000011 ',F, '2007/9/26 ', '13:00:00 ' union all
select '000011 ',F, '2007/9/26 ', '19:00:00 ' union all
select '000011 ',F, '2007/9/26 ', '23:00:00 ' union all
select '000011 ',F, '2007/9/27 ', '01:00:00 ' union all
select '000011 ',F, '2007/9/27 ', '07:00:00 ' union all
select '000011 ',F, '2007/9/27 ', '11:30:00 ' union all
select '000008 ',T, '2007/9/27 ', '07:30:00 ' union all
select '000008 ',T, '2007/9/27 ', '10:10:00 ' union all
select '000008 ',T, '2007/9/27 ', '12:30:00 ' union all
select '000011 ',F, '2007/9/27 ', '07:30:00 ' union all
select '000011 ',F, '2007/9/27 ', '10:10:00 ' union all
select '000011 ',F, '2007/9/27 ', '12:10:00 ' union all
要得到结果，B 字段的值是确定的。 他决定了 时间的处理方式。
设X为日期 T白班 (X的1:00(包含1:00)到X+1的00:30:00(包含X+1的00:30:00)) 跨度约23个半小时
设X为日期 F晚班 (X的13:00(包含13:00)到X+1的12:30:00(包含X+1的12:30:00)) 跨度约23个半小时
最后结果
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 07:30:00 10:10:00 11:30:00
000008 2007/9/27 07:30:00 10:10:00 12:30:00 NULL NULL NULL NULL NULL
在结果中忽略 多于的就是 超出8个的时间 如结果第二条记录中就有
select '000011 ',F, '2007/9/27 ', '12:10:00 ' union all 被忽略了。
希望大家一起帮帮忙。
[解决办法]
select idno,max(case when ictime between '01:00:00' and '03:00:00' then ictime else null end) as T1,
max(case when ictime between '03:00:01' and '07:00:00' then ictime else null end) as T2,
max(case when ictime between '07:00:01' and '10:00:00' then ictime else null end) as T3,
max(case when ictime between '10:00:01' and '13:00:00' then ictime else null end) as T4,
max(case when ictime between '13:00:01' and '16:00:00' then ictime else null end) as T5,
max(case when ictime between '16:00:01' and '19:00:00' then ictime else null end) as T6,
max(case when ictime between '19:00:01' and '22:00:00' then ictime else null end) as T7,
max(case when ictime between '22:00:01' and '23:59:59' then ictime else null end) as T8
from #A
group by idno
[解决办法]
我花了好长时间做上的，比较麻烦呀
drop table #A
drop table #tempTable
drop table #CountTable
drop table #T
create table #A(idno varchar(6),B varchar(1),icdate varchar(10),ictime varchar(8))
insert #A
select '000008','T','2007/9/26','01:00:00' union all
select '000008','T','2007/9/26','07:00:00' union all
select '000008','T','2007/9/26','12:30:00' union all
select '000008','T','2007/9/26','13:30:00' union all
select '000008','T','2007/9/26','17:00:00' union all
select '000008 ','T', '2007/9/26 ', '01:00:00 ' union all
select '000008 ','T', '2007/9/26 ', '07:00:00 ' union all
select '000008 ','T', '2007/9/26 ', '12:30:00 ' union all
select '000008 ','T', '2007/9/26 ', '13:30:00 ' union all
select '000008 ','T', '2007/9/26 ', '17:00:00 ' union all
select '000008 ','T', '2007/9/27 ', '00:30:00 ' union all
select '000011 ','F', '2007/9/26 ', '13:00:00 ' union all
select '000011 ','F', '2007/9/26 ', '19:00:00 ' union all
select '000011 ','F', '2007/9/26 ', '23:00:00 ' union all
select '000011 ','F', '2007/9/27 ', '01:00:00 ' union all
select '000011 ','F', '2007/9/27 ', '07:00:00 ' union all
select '000011 ','F', '2007/9/27 ', '11:30:00 ' union all
select '000008 ','T', '2007/9/27 ', '07:30:00 ' union all
select '000008 ','T', '2007/9/27 ', '10:10:00 ' union all
select '000008 ','T', '2007/9/27 ', '12:30:00 ' union all
select '000011 ','F', '2007/9/27 ', '07:30:00 ' union all
select '000011 ','F', '2007/9/27 ', '10:10:00 ' union all
select '000011 ','F', '2007/9/27 ', '12:10:00 '
select *,identity(int,1,1) as id into #tempTable from #A
update #tempTable
set icdate=replace(convert(nvarchar(10), CONVERT(DateTime, icdate)-1,111),'/0','/')
where B='F'
select count(idno) as [count],idno,icdate,identity(int,1,1) as id into #CountTable from #tempTable group by idno,icdate Order by icdate desc
create table #T(id int, idno varchar(6),icdate varchar(10),T1 varchar(8),T2 varchar(8),T3 varchar(8),T4 varchar(8),T5 varchar(8),T6 varchar(8),T7 varchar(8),T8 varchar(8))
declare @i int
declare @str varchar(200)
set @i=(select count(*) from #CountTable)
while @i>0
begin
Select Top 1 * into #singleTable From #CountTable Where id in (Select Top (@i) id From #CountTable Order by id asc ) Order by id desc
declare @j int
declare @k int
declare @count int
declare @sql varchar(500)
declare @idno varchar(6)
declare @icdate varchar(10)
set @k=1
set @j=(select [count] from #singleTable)
set @count=(select [count] from #singleTable)
set @idno=(select idno from #singleTable)
set @icdate=(select icdate from #singleTable)
insert into #T(id,idno,icdate) values ((@i),@idno,@icdate)
while @j>0 and @k<=8
begin
set @sql = ' declare @T'+ convert(varchar(5),@k)+' varchar(10) set @T'+ convert(varchar(5),@k)+ ' = (Select Top 1 ictime From #tempTable Where id in (Select Top '+convert(varchar(5), @count-@j+1)+' id From #tempTable where icdate= (select icdate from #singleTable) and idno= (select idno from #singleTable) ) Order by id Desc) update #T set T' +convert(varchar(5),@k)+ '=@T' +convert(varchar(5),@k)+' where id=' +convert(varchar(5),@i)
exec(@sql)
set @j=@j-1
set @k=@k+1
end
drop table #SingleTable
set @i=@i-1
end
select * from #T
[解决办法]
drop table #A
drop table #tempTable
drop table #CountTable
drop table #T
create table #A(idno varchar(6),B varchar(1),icdate varchar(10),ictime varchar(8))
insert #A
select '000008','T','2007/9/26','01:00:00' union all
select '000008','T','2007/9/26','07:00:00' union all
select '000008','T','2007/9/26','12:30:00' union all
select '000008','T','2007/9/26','13:30:00' union all
select '000008','T','2007/9/26','17:00:00' union all
select '000008 ','T', '2007/9/26 ', '01:00:00 ' union all
select '000008 ','T', '2007/9/26 ', '07:00:00 ' union all
select '000008 ','T', '2007/9/26 ', '12:30:00 ' union all
select '000008 ','T', '2007/9/26 ', '13:30:00 ' union all
select '000008 ','T', '2007/9/26 ', '17:00:00 ' union all
select '000008 ','T', '2007/9/27 ', '00:30:00 ' union all
select '000011 ','F', '2007/9/26 ', '13:00:00 ' union all
select '000011 ','F', '2007/9/26 ', '19:00:00 ' union all
select '000011 ','F', '2007/9/26 ', '23:00:00 ' union all
select '000011 ','F', '2007/9/27 ', '01:00:00 ' union all
select '000011 ','F', '2007/9/27 ', '07:00:00 ' union all
select '000011 ','F', '2007/9/27 ', '11:30:00 ' union all
select '000008 ','T', '2007/9/27 ', '07:30:00 ' union all
select '000008 ','T', '2007/9/27 ', '10:10:00 ' union all
select '000008 ','T', '2007/9/27 ', '12:30:00 ' union all
select '000011 ','F', '2007/9/27 ', '07:30:00 ' union all
select '000011 ','F', '2007/9/27 ', '10:10:00 ' union all
select '000011 ','F', '2007/9/27 ', '12:10:00 '
select *,identity(int,1,1) as id into #tempTable from #A
update #tempTable
set icdate=replace(convert(nvarchar(10), CONVERT(DateTime, icdate)-1,111),'/0','/')
where B='F'
select count(idno) as [count],idno,icdate,identity(int,1,1) as id into #CountTable from #tempTable group by idno,icdate Order by icdate desc
create table #T(id int, idno varchar(6),icdate varchar(10),T1 varchar(8),T2 varchar(8),T3 varchar(8),T4 varchar(8),T5 varchar(8),T6 varchar(8),T7 varchar(8),T8 varchar(8))
declare @i int
declare @str varchar(200)
set @i=(select count(*) from #CountTable)
while @i>0
begin
Select Top 1 * into #singleTable From #CountTable Where id in (Select Top (@i) id From #CountTable Order by id asc ) Order by id desc
declare @j int
declare @k int
declare @count int
declare @sql varchar(500)
declare @idno varchar(6)
declare @icdate varchar(10)
set @k=1
set @j=(select [count] from #singleTable)
set @count=(select [count] from #singleTable)
set @idno=(select idno from #singleTable)
set @icdate=(select icdate from #singleTable)
insert into #T(id,idno,icdate) values ((@i),@idno,@icdate)
while @j>0 and @k<=8
begin
set @sql = ' declare @T'+ convert(varchar(5),@k)+' varchar(10) set @T'+ convert(varchar(5),@k)+ ' = (Select Top 1 ictime From #tempTable Where id in (Select Top '+convert(varchar(5), @count-@j+1)+' id From #tempTable where icdate= (select icdate from #singleTable) and idno= (select idno from #singleTable) ) Order by id Desc) update #T set T' +convert(varchar(5),@k)+ '=@T' +convert(varchar(5),@k)+' where id=' +convert(varchar(5),@i)
exec(@sql)
set @j=@j-1
set @k=@k+1
end
drop table #SingleTable
set @i=@i-1
end
select * from #T
[解决办法]
粘了之后的确有毛病，可能是我粘在贴里，再复制时，有很多多余的空格，我 给 你发了短消息，你粘上试试，我花了很长时间的，这是结果，不知符不符合你你的要求。
40000112007/9/2513:00:0019:00:0023:00:00NULLNULLNULLNULLNULL
30000112007/9/2601:00:0007:00:0011:30:0007:30:0010:10:0012:10:00NULLNULL
20000082007/9/2601:00:0007:00:0012:30:0013:30:0017:00:0001:00:0007:00:0012:30:00
10000082007/9/27 00:30:0007:30:0010:10:0012:30:00NULLNULLNULLNULL
[解决办法]
哈哈，原来有这个东西，这回你粘上试试吧
[解决办法]
我在工作，无法上qq，可以skype:elvis_gao1，我没有看你的时间范围，只是把时间变成行，但是实现思路给你了，你加个条件，自己试试吧，我也是新手，我有时间再调调
[解决办法]
26 号 000008 的时间点范围范围是 (X >= 26号 01:00:00) and (X <= 27号 00:30:00)
26 号 000011 的时间点范围范围是 (X >= 26号 13:00:00) and (X <= 27号 12:30:00)
那么此时是否要考虑白班，晚班呢，
[解决办法]
也就是说你要的结果是
26 号 000008 的时间点范围范围是 (X >= 26号 01:00:00) and (X <= 27号 00:30:00)
26 号 000011 的时间点范围范围是 (X >= 26号 13:00:00) and (X <= 27号 12:30:00)
结果与F,T无关了
[解决办法]
学习啊 ！！！！！！！！！！！！！
