寻求帮忙---------没有遇到过的 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 countnumber
--------------------------- -----------
8 ~ 9 3
9 ~ 10 2
10 ~ 11 1
(3 row(s) affected)
[解决办法]
if exists (SELECT * FROM sysobjects where name = 'tt ' and type = 'U ')
drop table list
go
create table tt
(id int identity(1,1) primary key
,username nvarchar(10)
,starttime datetime
,endtime datetime
)
go
insert tt(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 '
select top 1000 Id=identity(int,0,1) into #t from sysobjects a,syscolumns b
select ltrim(datepart(hh,starttime))+ '~ '+ltrim(datepart(hh,endtime)) as 时间段,count(1) 用户数 from tt a, #t b
where ltrim(datepart(hh,endtime)-datepart(hh,starttime))> =b.id
group by ltrim(datepart(hh,starttime))+ '~ '+ltrim(datepart(hh,endtime))
时间段 用户数
------------------------- -----------
8~10 3
8~8 1
8~9 2
(3 行受影响)
