求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 被忽略了。
希望大家一起帮帮忙。
[解决办法]
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
