试了很多次,都没成功,请大家帮帮忙表一、members表
字段:memberID,memberName表二、articles表
字段:articleID,authorID(关联[members].memberID),views(被查看次数),title....现在要求做一个文章排行榜(以查看次数为标准),选出查看次数是前三名的,但是这三条记录的作者不能是同一个人,必须是三个不同的人。按查看次数从多到少排序。例如如果有一成员发表了两篇文章,分别查看次数是排在第一和第二,那么只选这人 个人排第一的文章,第二、第三是另外两个成员。
字段:memberID,memberName表二、articles表
字段:articleID,authorID(关联[members].memberID),views(被查看次数),title....现在要求做一个文章排行榜(以查看次数为标准),选出查看次数是前三名的,但是这三条记录的作者不能是同一个人,必须是三个不同的人。按查看次数从多到少排序。例如如果有一成员发表了两篇文章,分别查看次数是排在第一和第二,那么只选这人 个人排第一的文章,第二、第三是另外两个成员。
top 3 a.*
from
articles a
where
not exists(select 1 from articles where authorID=a.authorID and views>a.views)
top 3 a.*,m.memberName
from
members m,
articles a
where
m.memberID=a.authorID
and
not exists(select 1 from articles where authorID=a.authorID and views>a.views)
order by
a.views desc
字段:memberID,memberName表二、articles表
字段:articleID,authorID(关联[members].memberID),views(被查看次数),title....select a.memberid , a.membername , b.views
from members as a , (select top 3 dinstince authorid , views from articles order by views desc as b)
where a.memberid = b.authorid
from articles a
where articleID = (
select top 1 articleID from articles where authorID =
a.authorID order by views desc
)
order by views desc
select top 3 A.memberName,B.* from A inner join articles B on A.memberID=B.authorID
inner join
(select C.memberName,max(D.views) as views from members C inner join articles D on C.memberID=D.authorID
group by C.memberName) E
on A.memberName=E.memberName and B.views=E.views
order by E.views desc
from articles a,members
where a.authorID = memberID
and articleID = (
select top 1 articleID from articles where authorID = a.authorID
order by views desc
)
order by views desc
说一下运行结果:--libin_ftsafe(子陌红尘:当libin告别ftsafe) 结果:重复出现了成员--dawugui(潇洒老乌龟) 结果:运行出错,WHERE和AS B--shuiniu(飞扬的梦)(我是一头只吃西红柿的水牛) 结果:全是一个人的作品,已经加上distinct--playwarcraft(时间就像乳沟,挤挤还是有的) 结果:结果正确--shuiniu(飞扬的梦)(我是一头只吃西红柿的水牛):结果正确刚刚还有一个小问题忘记提了,例如就拿shuiniu(飞扬的梦)(我是一头只吃西红柿的水牛) 提供的方案来讲,我想查询一个类别里的这样排行,加了一个 “a.categoryID=3”结果就不对了,我想应该是加在这里吧select top 3 a.*,memberName
from articles a,members
where a.authorID = memberID AND a.categoryID=3
and articleID = (
select top 1 articleID from articles where authorID = a.authorID
order by views desc
)
order by views desc
select top 3 a.*,memberName
from articles a,members
where a.authorID = memberID AND a.categoryID=3
and articleID = (
select top 1 articleID from articles where authorID = a.authorID AND a.categoryID=3
order by views desc
)
order by views desc