MySQL进阶查询：找出可解锁最多画作的待购颜色

核心思路 #

我们的目标是找到添加后能直接解锁最多当前无法绘制画作的颜色，这类颜色的核心作用是补全那些仅缺1种颜色的画作——因为只有这类画作，单买一种颜色就能立即变为可绘制状态。聚焦这类场景能大幅提升计算效率，避免暴力遍历的性能问题。

具体SQL实现 #

第一步：定位仅缺1色的画作及对应缺失颜色 #

我们用CTE分层处理数据，先筛选出已拥有的颜色，再统计每幅画的颜色匹配情况，最后锁定仅缺1色的画作及其缺失的颜色：

WITH owned_colors AS (
    SELECT id FROM colors WHERE id IN (4,5,6,7,8,9,10,11,12,13,14) -- 替换为你的拥有颜色ID集合
),
painting_color_stats AS (
    SELECT
        pc.painting_id,
        COUNT(pc.color_id) AS total_colors,
        COUNT(oc.id) AS matched_colors,
        COUNT(pc.color_id) - COUNT(oc.id) AS missing_count
    FROM painting_colors pc
    LEFT JOIN owned_colors oc ON pc.color_id = oc.id
    GROUP BY pc.painting_id
),
missing_single_color_paintings AS (
    SELECT
        pcs.painting_id,
        pc.color_id AS missing_color_id
    FROM painting_color_stats pcs
    JOIN painting_colors pc ON pcs.painting_id = pc.painting_id
    LEFT JOIN owned_colors oc ON pc.color_id = oc.id
    WHERE pcs.missing_count = 1 AND oc.id IS NULL
)

第二步：统计每种缺失颜色的解锁能力 #

基于上面的结果，直接统计每种缺失颜色能解锁的画作数量，并按数量降序排列：

SELECT
    c.name AS color_name,
    mscp.missing_color_id AS color_id,
    COUNT(mscp.painting_id) AS unlocked_paintings_count
FROM missing_single_color_paintings mscp
JOIN colors c ON mscp.missing_color_id = c.id
GROUP BY mscp.missing_color_id, c.name
ORDER BY unlocked_paintings_count DESC;

方案优势 #

• 性能高效：无需重复执行原查询，一次计算即可得到结果，避免了暴力遍历所有缺失颜色的冗余操作
• 精准匹配需求：只针对能直接解锁画作的场景，避免了单纯统计颜色使用次数的偏差
• 逻辑清晰：CTE分层设计让查询逻辑一目了然，也便于数据库优化执行计划

可选扩展：评估颜色的潜在价值 #

如果需要考虑那些缺多种颜色的画作（添加颜色后虽不能直接解锁，但能减少后续解锁所需的颜色数量），可以用以下查询统计每种缺失颜色在无法绘制画作中的出现频率：

WITH owned_colors AS (
    SELECT id FROM colors WHERE id IN (4,5,6,7,8,9,10,11,12,13,14)
),
painting_missing_colors AS (
    SELECT
        pc.painting_id,
        pc.color_id AS missing_color_id
    FROM painting_colors pc
    LEFT JOIN owned_colors oc ON pc.color_id = oc.id
    WHERE oc.id IS NULL
)
SELECT
    c.name AS color_name,
    pmc.missing_color_id AS color_id,
    COUNT(pmc.painting_id) AS helps_unlock_count
FROM painting_missing_colors pmc
JOIN colors c ON pmc.missing_color_id = c.id
GROUP BY pmc.missing_color_id, c.name
ORDER BY helps_unlock_count DESC;

这个结果能帮你评估颜色的长期价值，但注意它和直接解锁的画作数量是两个不同的指标。

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