如何编写SQL查询识别conversions表中缺失的类型转换组合

核心思路 #

首先生成所有理论上应该存在的类型转换全量组合，再和已录入的conversions表数据做差集，剩下的就是未录入的缺失组合。

涉及表说明 #

• type表：存储类型字典，字段id、name，当前共有3种类型
• section表：存储分区字典，字段id、name，当前共有4个分区
• conversions表：存储已录入的转换关系，字段id、section_id、type_convert_from、type_convert_to

SQL实现 #

写法1：LEFT JOIN + IS NULL （兼容所有SQL语法） #

该写法适用性最广，还可以直接带出分区、类型的名称字段方便排查：

SELECT 
    s.id AS section_id,
    s.name AS section_name,
    t_from.id AS type_from_id,
    t_from.name AS type_from_name,
    t_to.id AS type_to_id,
    t_to.name AS type_to_name
FROM section s
-- 交叉关联所有源类型和目标类型，生成全量待校验组合
CROSS JOIN type t_from
CROSS JOIN type t_to
-- 关联已录入的转换关系
LEFT JOIN conversions c 
    ON c.section_id = s.id
    AND c.type_convert_from = t_from.id
    AND c.type_convert_to = t_to.id
-- 若业务不需要同类型互转（比如typeA转typeA），可去掉下一行注释过滤
-- WHERE t_from.id != t_to.id
-- 匹配不到的就是缺失的转换组合
AND c.id IS NULL;

写法2：EXCEPT 差集语法（支持PostgreSQL、SQL Server等数据库） #

如果只需要查缺失的ID组合，不需要关联名称，可以用更简洁的差集语法：

-- 全量理论组合
SELECT s.id AS section_id, t_from.id AS from_type, t_to.id AS to_type
FROM section s
CROSS JOIN type t_from
CROSS JOIN type t_to
-- 可选过滤同类型转换
-- WHERE t_from.id != t_to.id

EXCEPT

-- 已录入的组合
SELECT section_id, type_convert_from, type_convert_to
FROM conversions;

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