如何通过SQL获取所有关联a_id的a_status均为complete的c_id？

数据表结构与数据 #

表A（a_id, a_status） #

表B（c_id, a_id） #

需求说明 #

需要从表B中筛选出所有关联的a_id对应的a_status均为complete的c_id，示例中仅c_id=101符合要求。

原SQL问题分析 #

你尝试的SQL语句逻辑存在问题：

SELECT c_id
FROM b
WHERE a_id IN (
    SELECT a_id
    FROM a 
    WHERE status = "complete"
    GROUP BY a_id
    HAVING COUNT(b.c_id) = COUNT(*)
)

问题点：

1. 子查询中引用外部表b的c_id字段，与表A的分组逻辑不兼容，属于无效关联
2. 仅通过a_id IN (complete的a_id列表)只能筛选出包含至少一个complete状态的c_id，无法保证所有关联的a_id状态都是complete

正确实现方案 #

方案1：分组统计排除非complete项 #

SELECT b.c_id
FROM b
JOIN a ON b.a_id = a.a_id
GROUP BY b.c_id
HAVING SUM(CASE WHEN a.a_status != 'complete' THEN 1 ELSE 0 END) = 0;

逻辑：将表B与表A关联后按c_id分组，统计每个分组内非complete状态的数量，数量为0则说明该c_id关联的所有a_id状态都是complete。

方案2：用NOT EXISTS排除不符合项 #

SELECT DISTINCT b.c_id
FROM b
WHERE NOT EXISTS (
    SELECT 1
    FROM a
    WHERE a.a_id = b.a_id
    AND a.a_status != 'complete'
);

逻辑：找出所有不存在关联a_id状态为非complete的c_id，即所有关联a_id的状态均为complete。

方案3：对比分组总数与complete数量 #

SELECT b.c_id
FROM b
JOIN a ON b.a_id = a.a_id
GROUP BY b.c_id
HAVING COUNT(*) = SUM(CASE WHEN a.a_status = 'complete' THEN 1 ELSE 0 END);

逻辑：按c_id分组后，对比该分组的总a_id数量与其中状态为complete的数量，两者相等则说明所有a_id状态都是complete。

以上方案均可满足需求，可根据使用的数据库选择适配的写法。

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