如何用WITH子句和子查询解决Hackerrank哈利波特魔杖SQL问题？

题目核心要求拆解 #

1. 仅保留非邪恶魔杖（对应Wands_Property.is_evil = 0）
2. 对每一组(power, age)的魔杖，筛选出该组内coins_needed最低的所有记录
3. 输出字段：id、age、coins_needed、power
4. 结果排序规则：先按power降序，再按age降序

解法逻辑详解 #

要实现需求，核心是先定位每个(power, age)组合的最低金币花费，再反向匹配原数据拿到完整记录，步骤如下：

1. 关联基础数据：将存储魔杖核心属性的Wands表，和存储魔杖年限、邪恶标记的Wands_Property表通过code字段关联，同时过滤掉邪恶魔杖。
2. 计算分组最小花费：按power和age分组，用MIN(coins_needed)得到每个组合的最低金币数。
3. 匹配完整记录：将原关联数据和分组计算出的最小花费结果做关联，筛选出同时满足power、age、coins_needed匹配的记录，即可拿到每个组合下花费最低的魔杖的id等完整信息。

WITH子句实现方案 #

WITH子句（CTE公共表表达式）可将中间计算逻辑封装成临时表，让代码更清晰易读：

WITH min_coins_per_group AS (
    -- 计算每个(power, age)组合的最低金币花费
    SELECT 
        wp.age,
        w.power,
        MIN(w.coins_needed) AS min_coins
    FROM Wands w
    JOIN Wands_Property wp ON w.code = wp.code
    WHERE wp.is_evil = 0
    GROUP BY wp.age, w.power
)
-- 关联原数据，筛选出符合条件的记录
SELECT 
    w.id,
    wp.age,
    w.coins_needed,
    w.power
FROM Wands w
JOIN Wands_Property wp ON w.code = wp.code
JOIN min_coins_per_group m ON 
    wp.age = m.age 
    AND w.power = m.power 
    AND w.coins_needed = m.min_coins
WHERE wp.is_evil = 0
ORDER BY w.power DESC, wp.age DESC;

关键细节说明 #

• 两次过滤is_evil=0的原因：CTE中过滤是为了减少分组计算的数据量，主查询中过滤是避免关联时引入邪恶魔杖的冗余数据（也可仅在CTE中过滤，主查询通过关联自动继承，但双重过滤更严谨）。
• 若同一(power, age)组内有多个魔杖的coins_needed等于最小值，所有这些记录都会被保留，完全符合题目“每种组合下金币花费最低的记录”的要求。

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