如何编写SQL查询获取Table A中缺失指定Type的唯一ID

需求说明 #

Table A存储所有待检查的ID列表，Table B中每个ID最多关联3条记录，每条记录包含取值为1、2、3的Type字段。需编写SQL查询，传入指定Type列表后，返回Table A中至少缺失该列表中某一种Type记录的唯一ID。

示例数据 #

Table A #

包含ID：A、B、C、D、E

Table B #

不同Type组合的预期返回结果 #

• 传入Type列表(1)：返回E
• 传入Type列表(2)：返回C、D
• 传入Type列表(3)：返回B、D
• 传入Type列表(1,2)：返回C、D、E
• 传入Type列表(1,3)：返回B、D、E
• 传入Type列表(2,3)：返回B、C、D
• 传入Type列表(1,2,3)：返回B、C、D、E

可行SQL方案 #

方案1：生成全量ID-Type组合，筛选缺失项 #

核心思路是先生成Table A中所有ID与传入Type列表的笛卡尔积（每个ID对应列表中所有Type的组合），再找出这些组合中不存在于Table B的记录，最后去重得到目标ID。

以传入Type列表(1,2)为例：

WITH required_types AS (
    SELECT 1 AS type UNION ALL
    SELECT 2 AS type
)
SELECT DISTINCT a.id
FROM table_a a
CROSS JOIN required_types rt
WHERE NOT EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.id = a.id AND b.type = rt.type
);

若需动态传入Type列表，可根据所用数据库调整required_types部分（比如使用表变量、临时表或参数化查询）。

方案2：统计已存在Type数量，对比需求总数 #

通过统计每个ID在传入Type列表中的已存在Type数量，若数量小于列表长度，则说明该ID缺失至少一种Type。

以传入Type列表(1,2,3)为例：

WITH required_types AS (
    SELECT 1 AS type UNION ALL
    SELECT 2 AS type UNION ALL
    SELECT 3 AS type
),
required_count AS (
    SELECT COUNT(*) AS total FROM required_types
)
SELECT a.id
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND b.type IN (SELECT type FROM required_types)
GROUP BY a.id
HAVING COUNT(DISTINCT b.type) < (SELECT total FROM required_count);

方案3：使用EXCEPT筛选缺失组合 #

部分数据库支持EXCEPT关键字，可直接生成全量组合后减去已存在的组合，再提取ID：

WITH required_types AS (
    SELECT 1 AS type UNION ALL
    SELECT 2 AS type
)
SELECT DISTINCT id
FROM (
    SELECT a.id, rt.type
    FROM table_a a
    CROSS JOIN required_types rt
    EXCEPT
    SELECT id, type FROM table_b
) missing_combinations;

以上方案均可匹配预期结果，可根据数据库特性和个人习惯选择使用。

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