blog博客面视题目

[fgmis]

blog博客
用户表User 列userid userName
文章表article 列articleid articletitle articlecontent userid createdate
评论表critique 列critiqueid critiquecontent articleid userid
实现功能:返回最活跃用户的10篇文章
根据发边的公开文章最多的前10个用户,每个用户各取他们最新发表的文章,因此共10篇文章.
得到文章编号,文章标题,用户名称,评论数量

[Simba_Ji]

//取到最活跃的10个User
select top 10 userid
into #top10User
from
(
select userid ,count(articleid) as artcount
from article
group userid
) as article
order by artcount desc

//取出这10个User最晚的文章及回复数
select
user.userName ,art.articleid ,art.articletitle ,
max(art.createdate ) as createdate ,count(cri.*) as criCount
from #top10User tUser
inner join user on tUser.userid = user.userid
inner join article art on tUser.userid = art.userid
left join critiquecontent cri on cri.articleid = art.articleid
group by user.userName ,art.articleid ,art.articletitle

//这样应该就可以了

[echojiji]

SELECT TOP 10 b.articleid,b.articletitle,a.userName,
(SELECT COUNT(*)
FROM dbo.critique c
WHERE b.articleid=c.articleid)AS times
FROM dbo.Users a,
dbo.article b LEFT JOIN
(SELECT TOP 1 * FROM dbo.critique c) d
ON d.articleid=b.articleid
WHERE a.userid=b.userid ORDER BY createdate DESC