SQL需求：按ID取最小BidAmount并计算BidSuccess列

需为每个ID获取最小BidAmount值，并生成BidSuccess列，同时保留对应ID与TenderType。

测试数据SQL #

drop table if exists #temp
create table #temp (ID int,TenderType nvarchar(100),BidAmount int,isAwardCarrier bit) 
Insert into #temp (ID , TenderType, BidAmount, isAwardCarrier)
VALUES
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', 390, 0), 
(1, 'Spot Bid', 345, 1), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', 653.35, 0), 
(1, 'Ordinary', NULL, 1), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', 4150, 1), 
(2, 'Spot Bid', 3365, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', 3607.42, 0), 
(2, 'Ordinary', NULL, 1); 

预期输出 #

ID  TenderType  BidAmount       IsAwardCarrier  BidSuccess 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    390             0                   0 
1   Spot Bid    345             1                   1 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    653             0                   0 
1   Ordinary    NULL            1                   1 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    4150            1                   0 
2   Spot Bid    3365            0                   1 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    3607            0                   0 
2   Ordinary    NULL            1                   0 

BidSuccess计算逻辑 #

• 若某ID的BidAmount为最小值且isAwardCarrier=1，同时该ID存在TenderType='Ordinary'且isAwardCarrier=1的行，则这两行的BidSuccess=1（如ID=1）；
• 若BidAmount为最小值且isAwardCarrier=0，则该行的BidSuccess=1（如ID=2）。

实现SQL代码 #

WITH ID_Metrics AS (
    -- 计算每个ID的最小BidAmount，以及是否存在符合条件的Ordinary行
    SELECT 
        ID,
        MIN(BidAmount) AS MinBidAmount,
        CASE WHEN EXISTS (
            SELECT 1 
            FROM #temp t2 
            WHERE t2.ID = t1.ID 
              AND t2.TenderType = 'Ordinary' 
              AND t2.isAwardCarrier = 1
        ) THEN 1 ELSE 0 END AS HasQualifiedOrdinary
    FROM #temp t1
    WHERE BidAmount IS NOT NULL
    GROUP BY ID
)
SELECT 
    t.ID,
    t.TenderType,
    t.BidAmount,
    t.isAwardCarrier,
    -- 根据逻辑计算BidSuccess
    CASE 
        -- 逻辑1：最小BidAmount且isAwardCarrier=1，同时存在符合条件的Ordinary行
        WHEN t.BidAmount = im.MinBidAmount 
             AND t.isAwardCarrier = 1 
             AND im.HasQualifiedOrdinary = 1 THEN 1
        -- 逻辑1的Ordinary行分支：Ordinary行且isAwardCarrier=1，同时存在最小BidAmount且isAwardCarrier=1的行
        WHEN t.TenderType = 'Ordinary' 
             AND t.isAwardCarrier = 1 
             AND EXISTS (
                 SELECT 1 
                 FROM #temp t3 
                 WHERE t3.ID = t.ID 
                   AND t3.BidAmount = im.MinBidAmount 
                   AND t3.isAwardCarrier = 1
             ) THEN 1
        -- 逻辑2：最小BidAmount且isAwardCarrier=0
        WHEN t.BidAmount = im.MinBidAmount 
             AND t.isAwardCarrier = 0 THEN 1
        -- 其他情况设为0
        ELSE 0 END AS BidSuccess
FROM #temp t
JOIN ID_Metrics im ON t.ID = im.ID
ORDER BY t.ID, t.TenderType, t.BidAmount;

内容的提问来源于stack exchange，提问作者Red Devil