SQL查询：购总价≥3000的最便宜两件商品并获赠最贵第三件

原表数据 #

ProductName          Price  
straight jeans       1500  
slim jeans           2500  
Denim jacket         3000  
Denim shorts         800  
Skinny jeans         1700  
loose Jeans          2100  
mom Jeans            2800  
wide jeans           1850  
distressed jeans     1100  
bootcut jeans        1350

需求说明 #

活动规则：购买两件不同商品且总价≥3000，可获赠第三件商品。需要实现：

1. 找出花费最少的两件商品组合
2. 获取该组合对应的可获赠的最贵商品
3. 最终输出这三个商品的名称和价格（每行一个商品）

完整SQL实现 #

WITH ValidPairs AS (
    -- 生成所有符合总价要求的两件商品组合，避免重复配对
    SELECT 
        p1.ProductName AS Name1, p1.Price AS Price1,
        p2.ProductName AS Name2, p2.Price AS Price2,
        p1.Price + p2.Price AS TotalPrice
    FROM Products p1
    JOIN Products p2 ON p1.ProductName < p2.ProductName
    WHERE p1.Price + p2.Price >= 3000
),
CheapestPair AS (
    -- 筛选出总价最低的商品组合
    SELECT TOP 1 Name1, Price1, Name2, Price2
    FROM ValidPairs
    ORDER BY TotalPrice ASC
),
FreeItem AS (
    -- 找到不属于该组合的最贵赠品
    SELECT TOP 1 ProductName, Price
    FROM Products
    WHERE ProductName NOT IN (SELECT Name1 FROM CheapestPair)
      AND ProductName NOT IN (SELECT Name2 FROM CheapestPair)
    ORDER BY Price DESC
)
-- 合并输出三个商品的信息，按价格升序排列匹配期望格式
SELECT Name1 AS ProductName, Price1 AS Price FROM CheapestPair
UNION ALL
SELECT Name2 AS ProductName, Price2 AS Price FROM CheapestPair
UNION ALL
SELECT ProductName, Price FROM FreeItem
ORDER BY Price ASC;

逻辑解释 #

1. ValidPairs：通过自连接生成所有不重复的两件商品组合，同时过滤出总价≥3000的有效组合。
2. CheapestPair：从有效组合中按总价升序取第一组，得到花费最少的两件商品。
3. FreeItem：排除已选的两件商品，从剩余商品中选出价格最高的作为赠品。
4. 最后通过UNION ALL将三个商品的信息合并成三行，并按价格升序排列，得到与期望一致的输出结果。

输出结果 #

ProductName          Price
bootcut jeans        1350
Skinny jeans         1700
Denim jacket         3000

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