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
(
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
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
From Product P
Cross Apply
(
Select Top 2 * from ProductOrder O Where O.ProductID = P.ProductID
Order By OrderDateTime Desc
) As T