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

[fwq001]

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

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

[ojuju10]

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 行受影响)

[Ray_Zhang]

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'
select * from list



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[data]
GO

CREATE TABLE [dbo].[data] (
[时间段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[用户数] [int] NULL
) ON [PRIMARY]
GO
declare @inTemp int
declare @inMin int
declare @inMax int
declare @ind int
declare @i int

select @inMin=Min(datepart(hh,starttime))
from list

select @inMax=Max(datepart(hh,endtime))
from list

set @ind=@inMax-@inMin

set @i=0

select @inTemp=datepart(mi,endtime) from list where endtime = (select max(endtime) from list)

while @i<@ind
begin
insert into [dbo].[data]
select cast(cast(@inMin+@i as char(2))+'~'+cast(@inMin+@i+1 as char(2)) as char(5)) as '时间段',
Count(case when t.a>@i-1 then 1 end) as '用户数'
from
(select datepart(hh,endtime)-datepart(hh,starttime) as a
from list) as t
set @i=@i+1

if @inTemp>0 and @i=@ind
begin
insert into [dbo].[data]
select cast(cast(@inMin+@i as char(2))+'~'+cast(@inMin+@i+1 as char(2)) as char(5)) as '时间段',1
end
if @i>@ind
break
end

select * from data

[Ray_Zhang]

(1 row(s) affected)


(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)


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

(3 row(s) affected)

[巴拉莱卡]

mark

[gezhicyz]

漂亮！

[雪狐]

好强，学习一把

[巴拉莱卡]

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)

[巴拉莱卡]

名字打错了....没有+unicode的name

[fckadxz]

这种题，不要硬算。先建个表，字段是 时间段名 varhcar(20), 开始时间 datetime ,结束时间 datetime,和原始记录表作连接再分组，就比较轻松地得到解。