基于两列从SQL中只获取一个条目？

Question

我有一个如下的问题

select B.BidderId, I.TrackingNumber, B.Amount
from dbo.Items I
inner join dbo.Bids B on I.SaleId = B.SaleId
where I.item = '123' and I.IsRecycled = 0

基本上它将获取项目123的所有投标人，

示例结果

BidderID 1 => trackingnumber 1 => amount =>1000
BidderID 1 => trackingnumber 1 => amount =>9200
BidderID 1 => trackingnumber 1 => amount =>8000
BidderID 2 => trackingnumber 1 => amount =>8200
BidderID 1 => trackingnumber 2 => amount =>50

正如你所看到的，有多个出价金额的用户与bidderid 1。我如何才能只接受最大金额值为bidder1的行。

结果应该是这样的(只考虑用户对一个项目的最大金额)。

BidderID 1 => trackingnumber 1 => amount =>1000
BidderID 2 => trackingnumber 1 => amount =>8200
BidderID 1 => trackingnumber 2 => amount =>50 (this is a diffrent item so included)

Answer 1

这是一个简单的聚合：

select B.BidderId, I.TrackingNumber, MAX(B.Amount)
from dbo.Items I
inner join dbo.Bids B on I.SaleId = B.SaleId
where I.item = '123' and I.IsRecycled = 0
GROUP BY  B.BidderId, I.TrackingNumber

Answer 2

您可以尝试使用row_number()

select *
from (
    select B.BidderId, I.TrackingNumber, B.Amount
        , row_number() over (partition by B.BidderId, I.TrackingNumber order by B.Amount desc) as rn
    from dbo.Items I
    inner join dbo.Bids B On I.SaleId = B.SaleId
    where I.item = '123' and I.IsRecycled = 0
) A where rn = 1