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条就可以了~