--示例数据create table tb(emp int,starttime char(14),endtime char(14)) insert tb select 1,'20050501111212','20050501141200' go--分拆处理 select top 24 id=identity(int,0,1) into # from syscolumns select a.emp, starttime=case when b.id=0 then starttime else left(starttime,8) +replace(convert(char(8),dateadd(hour,b.id,stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':')),108),':','') end, endtime=case when b.id=datediff(hour, stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':'), stuff(stuff(stuff(endtime,9,0,' '),12,0,':'),15,0,':')) then endtime else left(starttime,8) +convert(char(2),dateadd(hour,b.id+1,stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':')),108)+'0000' end from tb a,# b where b.id<=datediff(hour, stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':'), stuff(stuff(stuff(endtime,9,0,' '),12,0,':'),15,0,':'))/*--结果emp starttime endtime ----------- ------------------------- ---------------- 1 20050501111212 20050501120000 1 20050501121212 20050501130000 1 20050501131212 20050501140000 1 20050501141212 20050501141200 (所影响的行数为 4 行) --*/ go drop table tb,#
declare @a table (emp int,begindate nvarchar(14),enddate nvarchar(14)) insert into @a select 1,'20050501111212','20050501141200'declare @emp int,@bt nvarchar(30),@et nvarchar(30) declare test cursor for select * from @a declare @t table (emp int,begindate datetime,endate datetime) open test fetch next from test into @emp,@bt,@et while @@fetch_status = 0 begin declare @temp datetime set @bt=stuff(stuff(stuff(stuff(stuff(@bt,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':') set @et=stuff(stuff(stuff(stuff(stuff(@et,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':') set @temp=dateadd(hh,1,left(@bt,13)+':00:00') while datediff(hh,@temp,@et)>=-1 begin if datediff(hh,@temp,@et)<0 insert into @t select @emp,@bt,@et else insert into @t select @emp,@bt,@temp set @bt=@temp set @temp=dateadd(hh,1,@temp) end fetch next from test into @emp,@bt,@et endclose test deallocate testselect * from @t
说一下啊
insert tb select 1,'20050501111212','20050501141200'
go--分拆处理
select top 24 id=identity(int,0,1) into # from syscolumns
select a.emp,
starttime=case when b.id=0 then starttime
else left(starttime,8)
+replace(convert(char(8),dateadd(hour,b.id,stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':')),108),':','')
end,
endtime=case when b.id=datediff(hour,
stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':'),
stuff(stuff(stuff(endtime,9,0,' '),12,0,':'),15,0,':'))
then endtime
else left(starttime,8)
+convert(char(2),dateadd(hour,b.id+1,stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':')),108)+'0000'
end
from tb a,# b
where b.id<=datediff(hour,
stuff(stuff(stuff(starttime,9,0,' '),12,0,':'),15,0,':'),
stuff(stuff(stuff(endtime,9,0,' '),12,0,':'),15,0,':'))/*--结果emp starttime endtime
----------- ------------------------- ----------------
1 20050501111212 20050501120000
1 20050501121212 20050501130000
1 20050501131212 20050501140000
1 20050501141212 20050501141200 (所影响的行数为 4 行)
--*/
go
drop table tb,#
insert into @a select 1,'20050501111212','20050501141200'declare @emp int,@bt nvarchar(30),@et nvarchar(30)
declare test cursor for select * from @a
declare @t table (emp int,begindate datetime,endate datetime)
open test
fetch next from test
into @emp,@bt,@et
while @@fetch_status = 0
begin
declare @temp datetime
set @bt=stuff(stuff(stuff(stuff(stuff(@bt,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':')
set @et=stuff(stuff(stuff(stuff(stuff(@et,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':')
set @temp=dateadd(hh,1,left(@bt,13)+':00:00')
while datediff(hh,@temp,@et)>=-1
begin
if datediff(hh,@temp,@et)<0
insert into @t select @emp,@bt,@et
else
insert into @t select @emp,@bt,@temp
set @bt=@temp
set @temp=dateadd(hh,1,@temp)
end
fetch next from test
into @emp,@bt,@et
endclose test
deallocate testselect * from @t
------------------------------------------
我想请问国外比较好和权威的英文SQL或者数据库论坛是什么?