按供应商ID获取最新的3个订单

Question

我有以下SQL Server代码来从4个表的组合中获取信息。

我想修改它，只检索供应商(pmpSupplierOrganizationID(的最新3个订单(pmpOrderDate(。

SELECT 
PO.pmpPurchaseOrderID, PO.pmpOrderDate, PO.pmpSupplierOrganizationID, O.cmoName
FROM 
PurchaseOrders PO 
INNER JOIN 
PurchaseOrderLines POL ON PO.pmpPurchaseOrderID = POL.pmlPurchaseOrderID 
INNER JOIN 
Organizations O ON PO.pmpSupplierOrganizationID = O.cmoOrganizationID 
INNER JOIN 
Parts P ON POL.pmlPartID = P.impPartID
WHERE 
P.impPartClassID LIKE 'PUMP%'

你能帮忙吗？

编辑：

我并不完全清楚自己的实际要求。为了进一步澄清，我最终需要的是根据PurchaseOrderLines中PartID的至少一个PartClassID，按供应商ID显示最新的3个唯一的采购订单，以字符串"PUMP"开头

Answer 1

使用ROW_NUMBER按pmpSupplierOrganizationID分区，按pmpOrderDate排序。

with cteTopOrders AS (
SELECT PO.pmpPurchaseOrderID, PO.pmpOrderDate, PO.pmpSupplierOrganizationID, O.cmoName,
ROW_NUMBER() OVER(PARTITION BY pmpSupplierOrganizationID ORDER BY pmpOrderDate DESC) AS RowNum
FROM PurchaseOrders PO 
Inner Join  PurchaseOrderLines POL ON PO.pmpPurchaseOrderID = POL.pmlPurchaseOrderID 
Inner Join Organizations O On PO.pmpSupplierOrganizationID = O.cmoOrganizationID 
Inner Join  Parts P ON POL.pmlPartID = P.impPartID
WHERE P.impPartClassID Like 'PUMP%'
)
SELECT pmpPurchaseOrderID, pmpOrderDate, pmpSupplierOrganizationID, cmoName
FROM cteTopOrders
WHERE RowNum <= 3;

Answer 2

我喜欢横向连接。cross apply:

select p.*, O.cmoName
from Organizations O cross apply
(select top (3) PO.pmpPurchaseOrderID, PO.pmpOrderDate, PO.pmpSupplierOrganizationID
from PurchaseOrders PO join
PurchaseOrderLines POL 
on PO.pmpPurchaseOrderID = POL.pmlPurchaseOrderID join
Parts P
on POL.pmlPartID = P.impPartID
where PO.pmpSupplierOrganizationID = O.cmoOrganizationID and
P.impPartClassID Like 'PUMP%'
order by PO.pmpOrderDate desc
) p

Answer 3

您需要一个嵌套的row_number来获得每个供应商的三行，以及它上面的另一个OLAP函数：

with OrderRowNum as
(
SELECT PO.pmpPurchaseOrderID, PO.pmpOrderDate, PO.pmpSupplierOrganizationID, O.cmoName, P.impPartClassID,
row_number()
over (partition by PO.pmpSupplierOrganizationID
order by pmpOrderDate desc) as rn
FROM PurchaseOrders PO 
Inner Join  PurchaseOrderLines POL ON PO.pmpPurchaseOrderID = POL.pmlPurchaseOrderID 
Inner Join Organizations O On PO.pmpSupplierOrganizationID = O.cmoOrganizationID 
Inner Join  Parts P ON POL.pmlPartID = P.impPartID
)
, CheckPUMP as
(
select *,
-- check if at least one of the three rows contains PUMP
max(case when impPartClassID Like 'PUMP%' then 1 else 0 end)
over (partition by PO.pmpSupplierOrganizationID) as PUMPflag
from OrderRowNum
where rn <= 3 -- get the last three rows per supplier
)
select * 
from CheckPUMP
where flag = 1