关于两个日期间

Question

declare @a table(ID varchar(20),开始时刻 smalldatetime,结束时刻 smalldatetime)
insert @a select '134217743','2007-01-22 23:00:06.000','2007-01-23 23:04:34.000'
union all select '134217838','2007-01-23 22:52:20.000','2007-01-23 23:04:44.000'select top 100 id=identity( int,0,1) into # from syscolumns a,syscolumns bselect a.id,
convert(varchar(10),dateadd(hour,b.id,开始时刻),120)日期,
datepart(hour,dateadd(hour,b.id,开始时刻)) 时间数
from @a a,# b
where datediff(hour,dateadd(hour,b.id,开始时刻),结束时刻)>=0
order by a.id,日期,时间数drop table #

Answer 1

--result
/*
id                   日期         时间数         
-------------------- ---------- ----------- 
134217743            2007-01-22 23
134217743            2007-01-23 0
134217743            2007-01-23 1
134217743            2007-01-23 2
134217743            2007-01-23 3
134217743            2007-01-23 4
134217743            2007-01-23 5
134217743            2007-01-23 6
134217743            2007-01-23 7
134217743            2007-01-23 8
134217743            2007-01-23 9
134217743            2007-01-23 10
134217743            2007-01-23 11
134217743            2007-01-23 12
134217743            2007-01-23 13
134217743            2007-01-23 14
134217743            2007-01-23 15
134217743            2007-01-23 16
134217743            2007-01-23 17
134217743            2007-01-23 18
134217743            2007-01-23 19
134217743            2007-01-23 20
134217743            2007-01-23 21
134217743            2007-01-23 22
134217743            2007-01-23 23
134217838            2007-01-23 22
134217838            2007-01-23 23（所影响的行数为 27 行）
*/

Answer 2

2007-01-22 23:00:06.000 拆分 成 2007-01-22  A ， 23 B 然后 按照 id ， A， B select 
……
Group By id  ， A，B