问:关系模式:User(userId, userName), Article(articleId, userId, title, content),Vote(articleId, score),User为用户关系,Article为用户发表的文章关系,Vote为文章得票关系,title为文章标题、score为得票数。 (3)用SQL语言查询出发表文章数大于5,文章平均得票数大于100的用户名,按平均得票数倒序排列; 这道题想不出来怎么写了 ,同学们 ! 有时间帮 帮忙的没 ? 谢谢 !
from Article as A inner join Vote as B
on A.articleID=B.articleID
group by A.UserID
having count(A.articleID)>5 and avg(B.score)>100
order by avgscore desc;
(select userId,count(0) ct from Article group by userID having count(0) >5)
,tp2 as (select tp1.userID,Article.articleId from Article join tp1 on tp1.userId=Article.userID),
tp3 as
(select articleId,avg(score) score from Vote GROUP BY articleId HAVING avg(score)>100) bselect USER.userName,tp3.score FROM tp1 JOIN USER ON tp1.userId=USER.userId
JOIN tp2 ON tp1.userId=tp2.UserId
JOIN tp3 ON tp2.articleId=tp3.articleId
ORDER BY tp3.score DESC;
(select userId,count(0) ct from Article group by userID having count(0) >5)
,tp2 as (select tp1.userID,Article.articleId from Article join tp1 on tp1.userId=Article.userID)
,tp3 as
(select articleId,avg(score) score from Vote GROUP BY articleId HAVING avg(score)>100)select USER.userName,tp3.score FROM tp1 JOIN USER ON tp1.userId=USER.userId
JOIN tp2 ON tp1.userId=tp2.UserId
JOIN tp3 ON tp2.articleId=tp3.articleId
ORDER BY tp3.score DESC;