取出每个分类的TOP 10
最近很多同事问类似问题,如何取出每个分类的TOP 10,现提供一种比较方便的方法,供大家参考。
示例数据库:AdventureWorks
比如:取出每个商品分类中最近发布的10条记录
1
SELECT * FROM
2
(
3![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
SELECT ROW_NUMBER() OVER (PARTITION BY ProductSubcategoryID ORDER BY ModifiedDate DESC) AS RowNumber,ProductID,Name,ProductSubcategoryID,ModifiedDate
5
FROM Production.Product WHERE ProductSubcategoryID IS NOT NULL
6
7
) T
8
WHERE RowNumber<=10
9![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这里主要是通过 ROW_NUMBER 创建一个条件出来。
大家有好的方法,也希望提供出来让大家学习学习。