1.創(chuàng)建表結(jié)構(gòu)和數(shù)據(jù)

創(chuàng)建表和數(shù)據(jù)Create?Table?Product?--產(chǎn)品表
(
ProductID?Int?Identity(1,1)?Primary?key,?--產(chǎn)品ID
ProductName?NVarchar(100)?Not?Null,?--產(chǎn)品名稱
ProductPrice?Int?Not?Null--價(jià)格
)
GO

Create?Table?ProductOrder?--產(chǎn)品訂單
(
OrderID?Int?Identity(1000,1)?Primary?key,
ProductID?Int,
Quantity?Int,--數(shù)量
amount?Int,--金額
OrderDateTime?DateTime?Not?Null?--下單時(shí)間
)
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.取出每個(gè)產(chǎn)品的前2條記錄

?? 2.1 使用ROW_NUMBER() 進(jìn)行排位分組

?

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

?

?

?

?

?

?

?

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/chinabc/archive/2011/03/11/1980632.html

總結(jié)

以上是生活随笔為你收集整理的SQL取出每个产品的Top n 条记录的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。