SQL取出每个产品的Top n 条记录

1.创建表结构和数据

Create Table Product --产品表
(
ProductID Int Identity(1,1) Primary key, --产品ID
ProductName NVarchar(100) Not Null, --产品名称
ProductPrice Int Not Null--价格
)
GO

Create Table ProductOrder --产品订单
(
OrderID Int Identity(1000,1) Primary key,
ProductID Int,
Quantity Int,--数量
amount Int,--金额
OrderDateTime DateTime Not Null --下单时间
)
Alter Table ProductOrder
Add Constraint FK_ProductOrder_ProductID Foreign Key(ProductID)
References Product(ProductID)
GO


Insert Into Product
Values('黄金',389.8)

Insert Into Product
Values('白银',8.9)

Insert Into ProductOrder
Values(1,10,3890,GetDate())

Insert Into ProductOrder
Values(2,1000,8900,GetDate())

Insert Into ProductOrder
Values(1,150,389.8*150,'2012-12-21')

Insert Into ProductOrder
Values(1,60000,388*60000,'2011-10-1')

Insert Into ProductOrder
Values(2,10000,88950,GetDate())

Insert Into ProductOrder
Values(2,1000,8850,GetDate())

Insert Into ProductOrder
Values(2,4000,32850,GetDate())

Select * from Product
Select * from ProductOrder

2.取出每个产品的前2条记录

   2.1 使用ROW_NUMBER() 进行排位分组

 

Select T.OrderID, P.ProductName, T.Quantity, T.Amount, T.OrderDateTime
From Product P
Left Join 
(
Select Row_Number() over(Partition By ProductID Order by OrderDateTime Desc) As RowID,
OrderID, ProductID,Quantity, Amount,OrderDateTime
From ProductOrder
) T On T.ProductID = P.ProductID
Where T.RowID<3

   2.2 使用Cross Apply

 

Select T.OrderID, P.ProductName, T.Quantity, T.Amount, T.OrderDateTime
From Product P
Cross Apply
(
Select Top 2 * from ProductOrder O Where O.ProductID = P.ProductID
Order By OrderDateTime Desc
) As T