SQL取出每类商品的前十条记录
2011-09-06 08:57 Eric.Hu 阅读(984) 评论(0) 编辑 收藏 举报取出每个分类的TOP 10,现提供一种比较方便的方法。
示例数据库:AdventureWorks
比如:取出每个商品分类中最近发布的10条记录
1 use adventureworks
2 go
3 select * from
4 (
5 SELECT ROW_NUMBER() OVER (PARTITION BY ProductSubcategoryID ORDER BY ModifiedDate DESC) AS RowNumber,ProductID,Name,ProductSubcategoryID,ModifiedDate FROM Production.Product WHERE ProductSubcategoryID IS NOT NULL
6 ) t
7 where rownumber <=10
3 select * from
4 (
5 SELECT ROW_NUMBER() OVER (PARTITION BY ProductSubcategoryID ORDER BY ModifiedDate DESC) AS RowNumber,ProductID,Name,ProductSubcategoryID,ModifiedDate FROM Production.Product WHERE ProductSubcategoryID IS NOT NULL
6 ) t
7 where rownumber <=10
着意耕耘,自有收获。