基于两列员工去重统计多状态数量的SQL实现问题

问题描述 #

我有一张包含staff1、staff2两个员工列以及status1、status2、status3三种状态字段的表，需要基于这两列员工去重后统计每位员工各状态的出现次数。

我的表 #

期望结果 #

我尝试的SQL语句（无法实现员工去重） #

SELECT DISTINCT
    CASE WHEN staff1 = staff2 THEN staff1
    WHEN staff1 <> staff2 THEN staff1
    WHEN staff2 <> staff1 THEN staff2
    ELSE staff2 AS staffall,
    SUM(CASE WHEN status1 = 'ACTIVE' THEN 1 ELSE 0)
    SUM(CASE WHEN status1 = 'CLOSED' THEN 1 ELSE 0)
    SUM(CASE WHEN status1 = 'NA' THEN 1 ELSE 0)
FROM Table
GROUP BY staff1, staff2

问题分析 #

原SQL存在两个核心问题：

1. 分组逻辑错误：按staff1和staff2分组会将同一员工在不同列的记录拆分成不同组，无法实现员工去重统计。
2. 状态统计不全：仅统计了status1的状态，完全忽略了status2和status3的数据。

解决方案 #

需要先将员工列和状态列分别展开为单行数据，再进行分组统计：

方案1（支持UNPIVOT的数据库，如SQL Server、Oracle） #

WITH staff_union AS (
    -- 将staff1和staff2拆分为单独的员工行，保留对应状态
    SELECT staff1 AS staff, status1, status2, status3 FROM your_table
    UNION ALL
    SELECT staff2 AS staff, status1, status2, status3 FROM your_table
),
status_union AS (
    -- 将三个状态列拆分为单独的状态值，过滤空状态
    SELECT staff, status
    FROM staff_union
    UNPIVOT (
        status FOR status_col IN (status1, status2, status3)
    ) AS unpvt
    WHERE status IS NOT NULL AND status != ''
)
-- 统计每位员工的各状态次数
SELECT 
    staff AS STAFF,
    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS ACTIVE,
    SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS CLOSED,
    SUM(CASE WHEN status = 'NA' THEN 1 ELSE 0 END) AS NA
FROM status_union
GROUP BY staff
ORDER BY staff;

方案2（通用SQL，适用于所有数据库） #

如果数据库不支持UNPIVOT，可以用UNION ALL手动拆分状态列：

WITH staff_status_union AS (
    -- 拆分staff1的所有状态
    SELECT staff1 AS staff, status1 AS status FROM your_table WHERE status1 IS NOT NULL AND status1 != ''
    UNION ALL
    SELECT staff1 AS staff, status2 AS status FROM your_table WHERE status2 IS NOT NULL AND status2 != ''
    UNION ALL
    SELECT staff1 AS staff, status3 AS status FROM your_table WHERE status3 IS NOT NULL AND status3 != ''
    -- 拆分staff2的所有状态
    UNION ALL
    SELECT staff2 AS staff, status1 AS status FROM your_table WHERE status1 IS NOT NULL AND status1 != ''
    UNION ALL
    SELECT staff2 AS staff, status2 AS status FROM your_table WHERE status2 IS NOT NULL AND status2 != ''
    UNION ALL
    SELECT staff2 AS staff, status3 AS status FROM your_table WHERE status3 IS NOT NULL AND status3 != ''
)
-- 统计每位员工的各状态次数
SELECT 
    staff AS STAFF,
    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS ACTIVE,
    SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS CLOSED,
    SUM(CASE WHEN status = 'NA' THEN 1 ELSE 0 END) AS NA
FROM staff_status_union
GROUP BY staff
ORDER BY staff;

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