SQL查询需求：展示所有员工的双表计数，含无记录员工

需要从Job_support和Job_interaction两张员工数据表生成统计报表，具体需求：

• 展示每位员工在两张表中的总计数
• 必须包含所有员工（包括两张表均无记录的员工，例如Amber）
• 缺失值按规则填充：计数类缺省填0，Unassigned对应的Job_interaction_cnt填"N/A"

表结构示例 #

表1 - Job_support #

CNT     Staff
1       Tom Smith
2       N/A

表2 - Job_interaction #

Staff       CNT
Tom Smith   1
Alice Adams 2

期望输出 #

Staff         Job_support_cnt   Job_interaction_cnt
Tom Smith     1                 1
Alice Adams   0                 2
Amber         0                 0
Unassigned    2                 N/A

尝试过的查询 #

查询1（仅返回交集员工） #

SELECT
    Job_interaction.staff,
    Job_interaction.cnt AS Job_interaction_cnt,
    Job_support_cnt.cnt AS Job_support_cnt
FROM
    Job_interaction
JOIN
    Job_support ON Job_support.staff = Job_interaction.staff;

返回结果仅包含两张表都有记录的员工：

Staff         Job_support_cnt   Job_interaction_cnt
Tom Smith     1                 1

查询2（接近需求但未处理特殊场景） #

SELECT
    COALESCE(Job_interaction.staff, Job_support.staff, 'Unassigned') AS Staff,
    COALESCE(Job_support.cnt, 0) AS Job_support_cnt,
    COALESCE(Job_interaction.cnt, 0) AS Job_interaction_cnt
FROM
    (SELECT DISTINCT staff FROM Job_support
     UNION
     SELECT DISTINCT staff FROM Job_interaction) StaffList
LEFT JOIN Job_support ON StaffList.staff = Job_support.staff
LEFT JOIN Job_interaction ON StaffList.staff = Job_interaction.staff

解决方案 #

核心修改点与用到的SQL特性 #

• 统一员工名称：将原表中Staff字段的"N/A"替换为"Unassigned"，避免分组歧义
• 补充额外员工：通过UNION手动添加不在两张表中的员工（如Amber）
• 精准缺失值填充：结合CASE WHEN和COALESCE处理不同场景的缺省值，满足需求中的显示规则
• 左连接保留所有员工：使用LEFT JOIN确保员工列表中的每一条记录都被保留，即使关联表无匹配

最终SQL查询 #

SELECT
    -- 将原表中的N/A统一转为Unassigned，保持名称一致性
    CASE 
        WHEN s_list.staff = 'N/A' THEN 'Unassigned'
        ELSE s_list.staff
    END AS Staff,
    -- Job_support缺失计数填0，Unassigned的原计数正常保留
    COALESCE(js.cnt, 0) AS Job_support_cnt,
    -- Unassigned的Job_interaction_cnt显示N/A，其他缺失填0
    CASE
        WHEN s_list.staff = 'N/A' THEN 'N/A'
        ELSE COALESCE(CAST(ji.cnt AS VARCHAR), '0')
    END AS Job_interaction_cnt
FROM
    -- 合并两张表的员工 + 手动添加Amber，生成完整员工列表
    (SELECT DISTINCT staff FROM Job_support
     UNION
     SELECT DISTINCT staff FROM Job_interaction
     UNION
     SELECT 'Amber' AS staff) s_list
-- 左连接Job_support，保留所有员工记录
LEFT JOIN Job_support js ON s_list.staff = js.staff
-- 左连接Job_interaction，保留所有员工记录
LEFT JOIN Job_interaction ji ON s_list.staff = ji.staff
ORDER BY Staff;

代码说明 #

1. 完整员工列表：通过三次SELECT的UNION生成包含两张表所有员工+Amber的列表，确保无遗漏
2. Staff字段格式化：用CASE WHEN把原表的"N/A"替换为"Unassigned"，统一显示名称
3. 计数填充逻辑：
   • Job_support_cnt：用COALESCE将关联不到的记录转为0，Unassigned的原计数2会被正常返回
   • Job_interaction_cnt：针对Unassigned特殊处理为"N/A"，其他情况用COALESCE把缺失值转为0（若cnt为数值类型，需转字符串匹配"N/A"格式）
4. 左连接的作用：LEFT JOIN保证员工列表中的每一条记录都能出现在结果中，不会因为关联表无匹配而被过滤

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