如何查询SQL表中未包含全部角色的分支及缺失角色

原始数据表 #

需求 #

找出未包含全部角色的分支及其缺失的角色：

• 分支A包含全部角色，无需输出
• 分支B缺失engineer角色
• 分支C缺失admin和finance角色

期望输出 #

实现SQL语句 #

WITH all_roles AS (
    SELECT DISTINCT role FROM employees
),
all_branches AS (
    SELECT DISTINCT branch FROM employees
)
SELECT ab.branch, ar.role
FROM all_branches ab
CROSS JOIN all_roles ar
LEFT JOIN employees e 
    ON ab.branch = e.branch AND ar.role = e.role
WHERE e.employee_id IS NULL
ORDER BY ab.branch, ar.role;

思路说明 #

1. 用两个CTE分别提取所有不重复的角色（all_roles）和所有不重复的分支（all_branches）
2. 通过CROSS JOIN生成所有分支与角色的可能组合
3. 用LEFT JOIN关联原始表，筛选出原始表中不存在的组合（即employee_id IS NULL的记录），这些就是各分支缺失的角色

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