求助一条sql语句.

[jianjialin]

declare @dt table(id int identity(1,1), [type] int, userid int, DateID int, HourID int)
-- type 观看类型, 
insert into @dt values
(1,1,20120720, 15),
(2,2,20120715, 23),
(3,3,20120713, 5),
(1,4,20120716, 4),
(2,1,20120714, 14),
(3,2,20120720, 17),
(1,2,20120718, 19),
(2,4,20120710, 5),
(3,4,20120720, 8),
(1,3,20120718, 15),
(2,5,20120719, 20)

select * from @dt

需求是
导出观看类型为1,2,3在7月15日—7月21日 17:00—22:00 , 23:00—04:00 , 05:00—10:00 , 11:00—16:00 四个时段的观看用户数.

预期结果是

问是否能一条语句搞定

[人生无悔]

--老兄，为别人提供语句好歹也建个正确的，没看明白你的153怎么出来的
declare @dt table(id int identity(1,1), [type] int, userid int, DateID int, HourID int)
insert into @dt select 1,1,20120720, 15
union all select 2,2,20120715, 23
union all select 3,3,20120713, 5
union all select 1,4,20120716, 4
union all select 2,1,20120714, 14
union all select 3,2,20120720, 17
union all select 1,2,20120718, 19
union all select 2,4,20120710, 5
union all select 3,4,20120720, 8
union all select 1,3,20120718, 15
union all select 2,5,20120719, 20
select * from @dt

[gw6328]

declare @dt table(id int identity(1,1), [type] int, userid int, DateID int, HourID int)
-- type 观看类型, 
insert into @dt values
(1,1,20120720, 15),
(2,2,20120715, 23),
(3,3,20120713, 5),
(1,4,20120716, 4),
(2,1,20120714, 14),
(3,2,20120720, 17),
(1,2,20120718, 19),
(2,4,20120710, 5),
(3,4,20120720, 8),
(1,3,20120718, 15),
(2,5,20120719, 20)

select 
	SUM(case when HourID between 17 and 22 then 1 else 0 end) as [17-22]
	,SUM(case when HourID between 0 and 4 or HourID =23 then 1 else 0 end) as [23-4]
	,SUM(case when HourID between 5 and 10 then 1 else 0 end) as [5-10]
	,SUM(case when HourID between 11 and 16 then 1 else 0 end) as [11-16]
from @dt where type in (1,2,3)
group by type
/*
17-22       23-4        5-10        11-16
----------- ----------- ----------- -----------
1           1           0           2
1           1           1           1
1           0           2           0
*/

[筱筱澄]

观看人数是怎么来的。

[筱筱澄]

观看人数是怎么来的。

[jianjialin]

[Quote=引用 2 楼 的回复:]

SQL code

declare @dt table(id int identity(1,1), [type] int, userid int, DateID int, HourID int)
-- type 观看类型,
insert into @dt values
(1,1,20120720, 15),
(2,2,20120715, 23),
(3,3,20120713, 5),
(1,……
[/Quote]

谢谢这个大哥的回复. 可是这样是无法排重的.

不好意思, 我应该说明一下.
这个表是用户观看的记录表. 维度精确到 某个用户(userid)在某个时间点(dateID, hourID)进行了某种行为(Type)的观看.

想分别获得每种观看行为在这四个时间段内的观看用户数(distinct UseriD)