SQL 查询——如何找到最低的 2 个数字

Question

我需要创建一个查询，为表中的每个唯一项目找到最低的 2 个值——我试图找到每个项目的前 2 个发货。

所以如果运输表有:

ID ---- Date --- PartID

1  ---- 1/1  ---- 1

2  ---- 1/2  ---- 2

3  ---- 1/2  ---- 1

4  ---- 1/3  ---- 1

我希望返回第 1、2 和 3 行，因为它们是每件商品的第一批和第二批 cargo 。

我可以创建一个获取最低 2 个值的查询:

Select Min(ShipmentID) as SID
from dbo.Shipment
UNION
Select Min(ShipmentID) as SID
from dbo.Shipment
where (ShipmentID > 
(Select Min(ShipmentID) 
from dbo.Shipment))

但是当我添加其他信息时，我只会得到每个项目的最低值，而不是两者:

Select Min(ShipmentID) as SID, AddressIDBilling
from dbo.Shipment
Group by AddressIDBilling
UNION
Select Min(ShipmentID) as SID, AddressIDBilling
from dbo.Shipment
where (ShipmentID > 
(Select Min(ShipmentID) 
from dbo.Shipment))
Group By AddressIDBilling
Order By AddressIDBilling

-- 每个 AddressID 只返回 1 行，而不是我想要的 2 条记录。

Answer 1

如果是 SQL 服务器，请使用 CTE 和 row_number()

with CTE as
(
select PartID, Date, row_number() over(partition by PartID order by Date) as PartOrd
from MyTable
)
select PartID, Date, PartOrd
from CTE
where PartOrd <=2