取出点击量最高的文章100篇，每个作者不超过5篇，如何写

select * from (select * from (select *,row_number() over (partition by author order by tips) rank from tb) t where rank<=100) T having count(T.author)<=5
------解决方案--------------------
探讨

SQL codeselect top 100 id,title,tips,author
from (
select *,cnt=row_number() over (partition by author order by tips desc)
from tb
) t
where cnt<=5
order by cnt desc

------解决方案--------------------
探讨

select top 100 * 
from tb 
where author in(select author from tb group by author having count(*) <=5) 
order by tips desc 


不对

------解决方案--------------------
探讨

请看我38楼回复,由于B作者有3个tips都为20,所以系统根本就分不出来那个是系统所要的2笔数据! 

你的告诉我们象这样的情况我们应该取那2笔数据! 

--- 
这个没要求，3个 tips都为 20 ，那随便取，就取编号大的两个吧。 

另外29楼的太慢了，数据也不对

------解决方案--------------------
SQL code
Select Top 100 * from Articles A Where Id in(select Top 5 id from Articles Where author = A.author Order by tips desc)Order By tips desc
------解决方案--------------------
我看到4楼,觉得改一个order就对,于是看到了6楼,非常好的利用了sql新特性,再下边就没看下去,抱歉,6楼的应该正确,row_number效率我试过100万记录飞快