sql 掏出分组后每组前N条记录

sql 取出分组后每组前N条记录
sql 取出分组后每组前N条记录

[解决办法]
select * from (select *,rank() over (partition by [分组的字段] order by [根据谁排序的字段] desc/asc)rn form [表名])temp where rn <N


[解决办法]
利用开窗函数,一条语句就可以了,思路如下:
1.利用ROW_NUMBER()OVER(partition BY [分组条件1]...[,分组条件n] ORDER BY [排序条件1]...[,排序条件n])
2.过滤出ROW_NUMBER小于等于N的数据
3.Sql这样大致:
SELECT * FROM (
SELECT 
SN = ROW_NUMBER()......(这里按照上面给出的公式)
,*
FROM [表]
) tmp
WHERE tmp.SN <=@MaxNumber (@MaxNumber就是你要的N)
[解决办法]

探讨

select * from (select *,rank() over (partition by [分组的字段] order by [根据谁排序的字段] desc/asc)rn form [表名])temp where rn <N

[解决办法]
看这个帖子 http://topic.csdn.net/u/20110325/13/3fefdb87-dd75-44a3-a9d2-e2ee6ea52b33.html
[解决办法]

探讨

引用:
select * from (select *,rank() over (partition by [分组的字段] order by [根据谁排序的字段] desc/asc)rn form [表名])temp where rn <N

应该是用row_number()吧。

[解决办法]
请参考MSDN关于开窗函数的文章:
http://msdn.microsoft.com/zh-cn/library/ms189461.aspx
[解决办法]
用临时表，先分组，后在取10条就可以了~

查看更多 下一篇