SQL实现：为缺失Director职位的role添加Vacant记录

现有SQL表结构 #

需求 #

编写SQL查询，当某个role不存在Director类型的designation时，新增一行记录：countries为NULL，output designation显示为VACANT。

例如当前表中IT角色无Director职位，需添加对应VACANT行，预期结果如下：

预期结果 #

SQL查询方案 #

-- 查询原表数据，映射输出字段
SELECT 
    role,
    designation,
    countries,
    designation AS `output designation`
FROM your_table_name

UNION ALL

-- 筛选缺少Director的role，生成VACANT记录
SELECT 
    role,
    NULL AS designation,
    NULL AS countries,
    'VACANT' AS `output designation`
FROM your_table_name
GROUP BY role
HAVING SUM(CASE WHEN designation = 'Director' THEN 1 ELSE 0 END) = 0;

说明 #

1. 第一部分直接读取原表所有数据，将原有designation作为output designation返回；
2. 第二部分通过分组统计每个role下Director职位的数量，筛选出无该职位的role，生成对应的空缺记录；
3. 用UNION ALL合并两部分结果，保留原数据同时补全空缺记录。

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