MySQL GROUP BY缺失值查询：基于A值存在的ID集合缺失值检测

嘿，这个需求是典型的行转列+存在性校验场景，咱们可以通过两种思路来实现，都能精准得到你想要的结果：

方案一：笛卡尔积+CASE表达式（扩展性好） #

这种方法适合可选Value数量较多的情况，后续要加新的Value选项时，只需要修改临时表vals的内容即可，不用大改查询逻辑：

SELECT
    base.ID,
    MAX(CASE WHEN val = 'B' THEN EXISTS (SELECT 1 FROM your_table t WHERE t.ID = base.ID AND t.Value = 'B') ELSE 0 END) AS B,
    MAX(CASE WHEN val = 'C' THEN EXISTS (SELECT 1 FROM your_table t WHERE t.ID = base.ID AND t.Value = 'C') ELSE 0 END) AS C,
    MAX(CASE WHEN val = 'D' THEN EXISTS (SELECT 1 FROM your_table t WHERE t.ID = base.ID AND t.Value = 'D') ELSE 0 END) AS D
FROM
    -- 先筛选出所有包含A值的目标ID集合
    (SELECT DISTINCT ID FROM your_table WHERE Value = 'A') base
-- 生成需要检查的所有Value选项（B、C、D）
CROSS JOIN
    (SELECT 'B' AS val UNION ALL SELECT 'C' UNION ALL SELECT 'D') vals
GROUP BY
    base.ID
ORDER BY
    base.ID;

方案二：多次左连接（逻辑直观） #

如果可选Value数量不多，这种方法更易读，逻辑链路清晰，新手也能快速理解：

SELECT
    base.ID,
    -- 检查每个Value是否存在，存在返回1，否则返回0
    CASE WHEN t_b.ID IS NOT NULL THEN 1 ELSE 0 END AS B,
    CASE WHEN t_c.ID IS NOT NULL THEN 1 ELSE 0 END AS C,
    CASE WHEN t_d.ID IS NOT NULL THEN 1 ELSE 0 END AS D
FROM
    -- 同样先筛选出含A值的ID集合
    (SELECT DISTINCT ID FROM your_table WHERE Value = 'A') base
-- 分别左连接每个Value对应的记录
LEFT JOIN your_table t_b ON base.ID = t_b.ID AND t_b.Value = 'B'
LEFT JOIN your_table t_c ON base.ID = t_c.ID AND t_c.Value = 'C'
LEFT JOIN your_table t_d ON base.ID = t_d.ID AND t_d.Value = 'D'
ORDER BY
    base.ID;

核心逻辑拆解 #

1. 锁定目标ID：通过SELECT DISTINCT ID FROM your_table WHERE Value = 'A'拿到所有需要检查的ID，题目明确每个存在的ID都包含A值，所以这个集合就是我们的处理对象。
2. 存在性判断：不管是用子查询的EXISTS，还是左连接后的IS NOT NULL，核心都是验证当前ID是否存在对应Value的记录。
3. 行转列处理：用CASE表达式把每个Value的存在状态转换成单独的列，再通过MAX（方案一）或者直接取左连接结果（方案二）得到最终的列值。

执行任意一种方案，都会得到你期望的结果：

ID | B | C | D
2 | 0 | 1 | 0
3 | 0 | 0 | 0

内容的提问来源于stack exchange，提问作者Zach M.