表 ArticleArticleID ArticleTitle UserName PostTime ...
2 标题一 csdn1 2006-9-18 15:38:29
3 标题2 csdn1 2006-9-18 15:41:29
4 标题3 csdn1 2006-9-18 15:42:29
5 标题4 csdn2 2006-9-18 15:47:29
6 标题5 csdn3 2006-9-18 15:52:29
7 标题6 csdn3 2006-9-18 15:58:29
......表 ReviewReviewID ArticleID ReviewTitle PostTime ...
2 2 标题1 2006-9-18 15:38:29
3 3 标题2 2006-9-18 15:41:29
4 5 标题3 2006-9-18 15:42:29
5 3 标题4 2006-9-18 15:47:29
6 6 标题5 2006-9-18 15:52:29
7 7 标题6 2006-9-18 15:58:29
......表 Article 是文章表,表 Review是评论表,表 User是用户表,
表 Review 中的ArticleID是对就于表 Article中的文章ID
表 Article中的UserName对应的是表 User中的UserName
现要求:
按照当天(2006-9-18)用户发表文章数量总和以及对其用户所发表的文章评论数量总和的降序排列出这些用户名称即: select UserName from *** where *** order by (这里是当天文章数量总和+当天对其用户所发表的文章评论数量总和) desc比如上面的要的结果列表是:
csdn1(3个文章+3个评论=6)
csdn3(2个文章+2个评论=4)
csdn2(1个文章+1个评论=2)(如果大家看不明白,我可以再说明白些)
2 标题一 csdn1 2006-9-18 15:38:29
3 标题2 csdn1 2006-9-18 15:41:29
4 标题3 csdn1 2006-9-18 15:42:29
5 标题4 csdn2 2006-9-18 15:47:29
6 标题5 csdn3 2006-9-18 15:52:29
7 标题6 csdn3 2006-9-18 15:58:29
......表 ReviewReviewID ArticleID ReviewTitle PostTime ...
2 2 标题1 2006-9-18 15:38:29
3 3 标题2 2006-9-18 15:41:29
4 5 标题3 2006-9-18 15:42:29
5 3 标题4 2006-9-18 15:47:29
6 6 标题5 2006-9-18 15:52:29
7 7 标题6 2006-9-18 15:58:29
......表 Article 是文章表,表 Review是评论表,表 User是用户表,
表 Review 中的ArticleID是对就于表 Article中的文章ID
表 Article中的UserName对应的是表 User中的UserName
现要求:
按照当天(2006-9-18)用户发表文章数量总和以及对其用户所发表的文章评论数量总和的降序排列出这些用户名称即: select UserName from *** where *** order by (这里是当天文章数量总和+当天对其用户所发表的文章评论数量总和) desc比如上面的要的结果列表是:
csdn1(3个文章+3个评论=6)
csdn3(2个文章+2个评论=4)
csdn2(1个文章+1个评论=2)(如果大家看不明白,我可以再说明白些)
解决方案 »
- 如何将数据库的设计结构用SQL语句提取出来保存在一个.sql文本文件中,然后运行这个文本文件重新生成一个新的空数据库,且与原来的数据库结构完全一样。
- Oracle中的DECODE如何转化为Sql server中的case when?
- Insert into 时出错。
- SQL 存储过程 如何循环列(求教大虾)
- 关于excel导入sql问题(续)
- 我的这个交叉表怎么实现?(在线)
- ADO 调用 SQL Server存储过程, xml数据类型如何处理?
- 紧急求助:SQL SERVER 2000问题
- 以最低的价格给你ocp的全部真题!
- 我要把图像,音频,视频文件存入数据库中
- 关于DTS(数据转换服务)的问题
- 请问怎么样用命令行来启动停止SQLServer下的某一数据库?
from (
select a.ArticleID ,a.UserName
from Article a
where datediff(d,a.PostTime ,getdate() )=0
union all
select C.Articleid ,C.username
from Article C join Review R on a.ArticleID=r.ArticleID
where datediff(d,a.PostTime ,getdate() )=0 and datediff(d,R.PostTime ,getdate() )=0
) D
group by D.Username
order by Count(D.Username)
没做过测试...
(ArticleID int,ArticleTitle varchar(20),UserName varchar(20),PostTime datetime)
declare @Review table
(ReviewID int,ArticleID varchar(20),ReviewTitle varchar(20),PostTime datetime)insert @Article
select 2,'标题一','csdn1','2006-9-18 15:38:29' union all
select 3,'标题2', 'csdn1','2006-9-18 15:41:29' union all
select 4,'标题3', 'csdn1','2006-9-18 15:42:29' union all
select 5,'标题4', 'csdn2','2006-9-18 15:47:29' union all
select 6,'标题5', 'csdn3','2006-9-18 15:52:29' union all
select 7,'标题6', 'csdn3','2006-9-18 15:58:29'insert @Review
select 2,2,'标题1','2006-9-18 15:38:29' union all
select 3,3,'标题2','2006-9-18 15:41:29' union all
select 4,5,'标题3','2006-9-18 15:42:29' union all
select 5,3,'标题4','2006-9-18 15:47:29' union all
select 6,6,'标题5','2006-9-18 15:52:29' union all
select 7,7,'标题6','2006-9-18 15:58:29'SELECT UserName,count(*) + sum(Reviews) as total FROM
(
select a.ArticleID,a.UserName,b.Reviews from @Article a
left join
(select ArticleID,count(*) as Reviews from @Review
where datediff(dd,PostTime,getdate()) = 0 /*指定评论表的时间*/
group by ArticleID) b
on a.ArticleID = b.ArticleID
where datediff(dd,a.PostTime,getdate()) = 0 /*指定文章表的时间*/
) c
GROUP BY UserName ORDER BY 2 DESC/*结果
UserName total
csdn1 6
csdn3 4
csdn2 2
*/
declare @Article table
(ArticleID int,ArticleTitle varchar(20),UserName varchar(20),PostTime datetime)
declare @Review table
(ReviewID int,ArticleID varchar(20),ReviewTitle varchar(20),PostTime datetime)insert @Article
select 2,'标题一','csdn1','2006-9-18 15:38:29' union all
select 3,'标题2', 'csdn1','2006-9-18 15:41:29' union all
select 4,'标题3', 'csdn1','2006-9-18 15:42:29' union all
select 5,'标题4', 'csdn2','2006-9-18 15:47:29' union all
select 6,'标题5', 'csdn3','2006-9-18 15:52:29' union all
select 7,'标题6', 'csdn3','2006-9-18 15:58:29'insert @Review
select 2,2,'标题1','2006-9-18 15:38:29' union all
select 3,3,'标题2','2006-9-18 15:41:29' union all
select 4,5,'标题3','2006-9-18 15:42:29' union all
select 5,3,'标题4','2006-9-18 15:47:29' union all
select 6,6,'标题5','2006-9-18 15:52:29' union all
select 7,7,'标题6','2006-9-18 15:58:29'SELECT UserName,count(*) + sum(Reviews) as total FROM
(
select a.ArticleID,a.UserName,b.Reviews from @Article a
left join
(select ArticleID,count(*) as Reviews from @Review
where datediff(dd,PostTime,getdate()) = 0 /*指定评论表的时间*/
group by ArticleID) b
on a.ArticleID = b.ArticleID
where datediff(dd,a.PostTime,getdate()) = 0 /*指定文章表的时间*/
) c
GROUP BY UserName ORDER BY 2 DESC/*结果
UserName total
csdn1 6
csdn3 4
csdn2 2
*/
-------------------------------------------------------当表 Review没有对表 Article的评论时,得来是null值
declare @Article table
(ArticleID int,ArticleTitle varchar(20),UserName varchar(20),PostTime datetime)
declare @Review table
(ReviewID int,ArticleID varchar(20),ReviewTitle varchar(20),PostTime datetime)insert @Article
select 2,'标题一','csdn1','2006-9-18 15:38:29' union all
select 3,'标题2', 'csdn1','2006-9-18 15:41:29' union all
select 4,'标题3', 'csdn1','2006-9-18 15:42:29' union all
select 5,'标题4', 'csdn2','2006-9-18 15:47:29' union all
select 6,'标题5', 'csdn3','2006-9-18 15:52:29' union all
select 7,'标题6', 'csdn3','2006-9-18 15:58:29' union all
select 8,'标题7', 'csdn4','2006-9-18 15:58:29'insert @Review
select 2,2,'标题1','2006-9-18 15:38:29' union all
select 3,3,'标题2','2006-9-18 15:41:29' union all
select 4,5,'标题3','2006-9-18 15:42:29' union all
select 5,3,'标题4','2006-9-18 15:47:29' union all
select 6,6,'标题5','2006-9-18 15:52:29' union all
select 7,7,'标题6','2006-9-18 15:58:29'declare @now datetime
set @now = '2006-9-18' /*设置统计日期变量*/
SELECT UserName,isnull(count(*) + sum(Reviews),0) /*使用isnull()函数*/
as total FROM
(
select a.ArticleID,a.UserName,b.Reviews from @Article a
left join
(select ArticleID,count(*) as Reviews from @Review
where datediff(dd,PostTime,@now) = 0 /*指定评论表的时间*/
group by ArticleID) b
on a.ArticleID = b.ArticleID
where datediff(dd,a.PostTime,@now) = 0 /*指定文章表的时间*/
) c
GROUP BY UserName ORDER BY 2 DESC/*结果 当表 Review没有对表 Article的评论时,得来是null值
UserName total
csdn1 6
csdn3 4
csdn2 2
csdn4 0
*/
这样试试:
declare @Article table
(ArticleID int,ArticleTitle varchar(20),UserName varchar(20),PostTime datetime)
declare @Review table
(ReviewID int,ArticleID varchar(20),ReviewTitle varchar(20),PostTime datetime)insert @Article
select 2,'标题一','csdn1','2006-9-18 15:38:29' union all
select 3,'标题2', 'csdn1','2006-9-18 15:41:29' union all
select 4,'标题3', 'csdn1','2006-9-18 15:42:29' union all
select 5,'标题4', 'csdn2','2006-9-18 15:47:29' union all
select 6,'标题5', 'csdn3','2006-9-18 15:52:29' union all
select 7,'标题6', 'csdn3','2006-9-18 15:58:29' union all
select 8,'标题7', 'csdn4','2006-9-18 15:58:29'insert @Review
select 2,2,'标题1','2006-9-18 15:38:29' union all
select 3,3,'标题2','2006-9-18 15:41:29' union all
select 4,5,'标题3','2006-9-18 15:42:29' union all
select 5,3,'标题4','2006-9-18 15:47:29' union all
select 6,6,'标题5','2006-9-18 15:52:29' union all
select 7,7,'标题6','2006-9-18 15:58:29'declare @now datetime
set @now = '2006-9-18' /*设置统计日期变量*/
SELECT UserName,isnull(count(*) + sum(Reviews),0) /*使用isnull()函数*/
as total FROM
(
select a.ArticleID,a.UserName,b.Reviews from @Article a
left join
(select ArticleID,count(*) as Reviews from @Review
where datediff(dd,PostTime,@now) = 0 /*指定评论表的时间*/
group by ArticleID) b
on a.ArticleID = b.ArticleID
where datediff(dd,a.PostTime,@now) = 0 /*指定文章表的时间*/
) c
GROUP BY UserName ORDER BY 2 DESC/*结果 当表 Review没有对表 Article的评论时,得来是null值
UserName total
csdn1 6
csdn3 4
csdn2 2
csdn4 0
*/
----------------------------------------
如果文章不是当天发布的,而评论是当天的,就统计不来了,因为当天的评论也要算在内的根据xiaoku(小苦走了,小酷来了!) 改成:select
d.UserName ,Count(d.UserName)
from
(select
a.ArticleID ,a.UserName
from
Article a
where
datediff(dd,a.PostTime ,getdate() )=0
union all
select
c.ArticleID ,c.UserName
from
Article c left join Review r on r.ArticleID=c.ArticleID
where
datediff(dd,r.PostTime ,getdate())=0
) d
group by
d.UserName
order by
Count(d.UserName) desc解决了问题,多谢两位..
d.UserName ,Count(d.UserName)
from
(select
a.ArticleID ,a.UserName
from
Article a
where
datediff(dd,a.PostTime ,getdate() )=0
union all
select
c.ArticleID ,c.UserName
from
Review r left join Article c on r.ArticleID=c.ArticleID
where
datediff(dd,r.PostTime ,getdate())=0
) d
group by
d.UserName
order by
Count(d.UserName) desc这样似乎好些...结贴..
where datediff(dd,PostTime,@now) = 0 /*指定评论表的时间*/