34,588
社区成员
发帖
与我相关
我的任务
分享
create table 评论表(ID int,视频ID int,评论内容 varchar(20),评论时间 datetime)
insert into 评论表 values(1, 1 ,'还行', '2012-03-07 12:01:02')
insert into 评论表 values(2, 1 ,'不行', '2012-03-08 13:11:22')
insert into 评论表 values(3, 2 ,'不错', '2012-03-08 12:14:26')
create table 点击率表(ID int,视频ID int,点击时间 datetime)
insert into 点击率表 values(1, 1 ,'2012-03-07 01:02:03')
insert into 点击率表 values(2, 1 ,'2012-03-08 15:04:09')
insert into 点击率表 values(3, 3 ,'2012-03-08 18:12:14')
go
select isnull(isnull(isnull(t1.视频ID,t2.视频ID),t3.视频ID),t4.视频ID) 视频ID,
isnull(t1.日点击量,0) 日点击量,
isnull(t2.日评论量,0) 日评论量,
isnull(t3.周点击量,0) 周点击量,
isnull(t4.周评论量,0) 周评论量
from
(select 视频ID , count(1) 日点击量 from 点击率表 where datediff(dd,getdate(),点击时间) = 0 group by 视频ID) t1
full join
(select 视频ID , count(1) 日评论量 from 评论表 where datediff(dd,getdate(),评论时间) = 0 group by 视频ID) t2
on t1.视频ID = t2.视频ID
full join
(select 视频ID , count(1) 周点击量 from 点击率表 where datediff(week,getdate(),点击时间) = 0 group by 视频ID) t3
on isnull(t1.视频ID,t2.视频ID) = t3.视频ID
full join
(select 视频ID , count(1) 周评论量 from 评论表 where datediff(week,getdate(),评论时间) = 0 group by 视频ID) t4
on isnull(isnull(t1.视频ID,t2.视频ID),t3.视频ID) = t4.视频ID
drop table 评论表 ,点击率表
/*
视频ID 日点击量 日评论量 周点击量 周评论量
----------- ----------- ----------- ----------- -----------
1 1 1 2 2
2 0 1 0 1
3 1 0 1 0
(所影响的行数为 3 行)
*/
--修正一下
--> 测试数据: @评论表
declare @评论表 table (ID int,视频ID int,评论内容 varchar(4),评论时间 datetime)
insert into @评论表
select 1,1,'还行','2012-03-07 12:01:02' union all
select 2,1,'不行','2012-03-08 13:11:22' union all
select 3,2,'不错','2012-03-08 12:14:26'
--> 测试数据: @点击率表
declare @点击率表 table (ID int,视频ID int,点击时间 datetime)
insert into @点击率表
select 1,1,'2012-03-07 01:02:03' union all
select 2,1,'2012-03-08 15:04:09' union all
select 3,3,'2012-03-08 18:12:14'
SELECT ISNULL(a.视频ID, b.视频ID) AS 视频ID ,
ISNULL(a.当天评论, 0) AS 当天评论 ,
ISNULL(a.一周内评论, 0) AS 一周内评论 ,
ISNULL(b.当天点击, 0) AS 当天点击 ,
ISNULL(b.一周内点击, 0) AS 一周内点击
FROM ( SELECT 视频ID ,
SUM(CASE WHEN DATEDIFF(d, 评论时间, GETDATE()) = 0 THEN 1
ELSE 0
END) AS 当天评论 ,
SUM(CASE WHEN DATEDIFF(d, 评论时间, GETDATE()) < 7 THEN 1
ELSE 0
END) AS 一周内评论
FROM @评论表
GROUP BY 视频ID
) a
FULL JOIN ( SELECT 视频ID ,
SUM(CASE WHEN DATEDIFF(d, 点击时间, GETDATE()) = 0
THEN 1
ELSE 0
END) AS 当天点击 ,
SUM(CASE WHEN DATEDIFF(d, 点击时间, GETDATE()) < 7
THEN 1
ELSE 0
END) AS 一周内点击
FROM @点击率表
GROUP BY 视频ID
) b ON a.视频ID = b.视频ID
/*
视频ID 当天评论 一周内评论 当天点击 一周内点击
----------- ----------- ----------- ----------- -----------
1 1 2 1 2
2 1 1 0 0
3 0 0 1 1
*/
--> 测试数据: @评论表
declare @评论表 table (ID int,视频ID int,评论内容 varchar(4),评论时间 datetime)
insert into @评论表
select 1,1,'还行','2012-03-07 12:01:02' union all
select 2,1,'不行','2012-03-08 13:11:22' union all
select 3,2,'不错','2012-03-08 12:14:26'
--> 测试数据: @点击率表
declare @点击率表 table (ID int,视频ID int,点击时间 datetime)
insert into @点击率表
select 1,1,'2012-03-07 01:02:03' union all
select 2,1,'2012-03-08 15:04:09' union all
select 3,3,'2012-03-08 18:12:14'
SELECT a.*,b.当天点击,b.一周内点击 FROM (
SELECT ID ,
SUM(CASE WHEN DATEDIFF(d, 评论时间, GETDATE()) = 0 THEN 1
ELSE 0
END) AS 当天评论 ,
SUM(CASE WHEN DATEDIFF(d, 评论时间, GETDATE()) < 0 THEN 1
ELSE 0
END) AS 一周内评论
FROM @评论表
GROUP BY ID
) a LEFT JOIN (
SELECT ID ,
SUM(CASE WHEN DATEDIFF(d, 点击时间, GETDATE()) = 0 THEN 1
ELSE 0
END) AS 当天点击 ,
SUM(CASE WHEN DATEDIFF(d, 点击时间, GETDATE()) < 0 THEN 1
ELSE 0
END) AS 一周内点击
FROM @点击率表
GROUP BY ID) b ON a.id=b.id
/*
ID 当天评论 一周内评论 当天点击 一周内点击
----------- ----------- ----------- ----------- -----------
1 0 0 0 0
2 1 0 1 0
3 1 0 1 0
*/