寻求帮忙---------没有遇到过的 SQL
table

id         username       starttime                             endtime
1           fw                   2007-8-16   8:00:00               2007-8-16   8:45:00
2           re                   2007-8-16   8:30:00               2007-8-16   9:45:00
3           wq                   2007-8-16   8:45:00               2007-8-16   10:15:00

注意点：如果用户从开始到结束时间段内属于某几个小时段内，则这个用户在这几个小时段内都要计数一次

问题是   求2007-8-16   日每个小时段内的用户     的SQL
结果如下

时间段     用户数
8～9         3
9～10       2
10～11     1

谢谢浏览，请留下脚印，期待答案的出现，谢谢！

------解决方案--------------------

if exists (SELECT * FROM sysobjects where name = 'list ' and type = 'U ')
drop table list
go

create table list
(id int identity(1,1) primary key
,username nvarchar(10)
,starttime datetime
,endtime datetime
)
go

insert list(username,starttime,endtime)
select 'fw ', '2007-8-16 8:00:00 ', '2007-8-16 8:45:00 '
union
select 're ', '2007-8-16 8:30:00 ', '2007-8-16 9:45:00 '
union
select 'fw ', '2007-8-16 8:45:00 ', '2007-8-16 10:15:00 '

if exists (SELECT * FROM sysobjects where name = 'timelist ' and type = 'U ')
drop table timelist
go

create table timelist
(id int identity(1,1) primary key
,starthour int
,endhour int
)
go

insert timelist(starthour,endhour)
select 8,9
union select 9,10
union select 10,11


select * from list
select * from timelist


select cast(timelist.starthour as nvarchar) + ' ~ '+ cast(timelist.endhour as nvarchar) as hours
,count(b.username) as countnumber
from timelist
left outer join
(
select id
,username
,datepart(hour,starttime) as starttime
,case datepart(minute,endtime) when 0 then datepart(hour,endtime) else datepart(hour,endtime)+1 end as endtime from list
where
convert(char(10),starttime,120) = '2007-08-16 '
and convert(char(10),starttime,120) = '2007-08-16 '
) as b
on
timelist.starthour > = B.STARTTIME AND timelist.endhour <= endtime
group by timelist.id,timelist.starthour,timelist.endhour
------解决方案--------------------

id username starttime endtime
----------- ---------- ----------------------- -----------------------
1 fw 2007-08-16 08:00:00.000 2007-08-16 08:45:00.000
2 fw 2007-08-16 08:45:00.000 2007-08-16 10:15:00.000
3 re 2007-08-16 08:30:00.000 2007-08-16 09:45:00.000

(3 row(s) affected)

id starthour endhour
----------- ----------- -----------
1 8 9
2 9 10
3 10 11

(3 row(s) affected)

hours