跨多列统计distinct值：现有SQL方案优化需求问询

需求说明 #

需要按项目统计分散在员工1、员工2、员工3列中的不同员工数量，同时汇总每个项目的总成本。现有实现方法较为繁琐，且因对cost列的非对称处理，担忧存在重复计数问题，寻求更简洁可靠的方案。

示例输入 #

预期输出 #

当前实现方法 #

SELECT project
    , COUNT(DISTINCT staff) AS num_distinct_staff
    , SUM(cost) AS total_cost
FROM (
    SELECT project, staff_1 AS staff, cost AS cost FROM table UNION ALL
    SELECT project, staff_2 AS staff, NULL AS cost FROM table UNION ALL
    SELECT project, staff_3 AS staff, NULL AS cost FROM table
) AS sub
GROUP BY project

当前方法的问题分析 #

• 写法繁琐，若新增员工列（如员工4），需手动添加新的UNION ALL分支，扩展性差；
• 对cost列的非对称处理虽不会导致重复计数（SUM(NULL)结果为0，最终总和等价于原始表的SUM(cost)），但逻辑不够直观，易引发误解。

更优方案 #

方案1：分模块统计后关联（通用所有SQL数据库） #

将成本汇总和员工去重统计分开处理，再通过项目关联，逻辑清晰且避免重复计数风险：

WITH project_total_cost AS (
    -- 单独汇总每个项目的总成本
    SELECT project, SUM(cost) AS total_cost
    FROM table
    GROUP BY project
),
project_distinct_staff AS (
    -- 统计每个项目的不同员工数量
    SELECT project, COUNT(DISTINCT staff) AS num_distinct_staff
    FROM (
        SELECT project, staff_1 AS staff FROM table WHERE staff_1 IS NOT NULL
        UNION ALL
        SELECT project, staff_2 AS staff FROM table WHERE staff_2 IS NOT NULL
        UNION ALL
        SELECT project, staff_3 AS staff FROM table WHERE staff_3 IS NOT NULL
    ) AS staff_list
    GROUP BY project
)
-- 关联两个结果集
SELECT 
    ptc.project,
    pds.num_distinct_staff,
    ptc.total_cost
FROM project_total_cost ptc
JOIN project_distinct_staff pds ON ptc.project = pds.project;

方案2：使用UNPIVOT（适用于SQL Server、Oracle等支持的数据库） #

通过UNPIVOT将多列员工数据转为行，再统一处理，写法更简洁：

WITH unpivoted_staff AS (
    SELECT project, cost, staff
    FROM table
    UNPIVOT (
        staff FOR staff_cols IN (staff_1, staff_2, staff_3)
    ) AS up
)
SELECT 
    project,
    COUNT(DISTINCT staff) AS num_distinct_staff,
    -- 用DISTINCT确保成本不重复计算，因UNPIVOT后每行原始数据会生成3条记录
    SUM(DISTINCT cost) AS total_cost
FROM unpivoted_staff
WHERE staff IS NOT NULL
GROUP BY project;

注意：若同一项目存在多行相同成本的记录，SUM(DISTINCT cost)会导致统计错误，此时建议优先使用方案1。

方案3：使用CROSS APPLY/UNNEST（适用于SQL Server、PostgreSQL等） #

SQL Server 版本

WITH staff_list AS (
    SELECT project, cost, staff
    FROM table
    CROSS APPLY (
        VALUES (staff_1), (staff_2), (staff_3)
    ) AS vals(staff)
    WHERE staff IS NOT NULL
)
SELECT 
    project,
    COUNT(DISTINCT staff) AS num_distinct_staff,
    SUM(DISTINCT cost) AS total_cost
FROM staff_list
GROUP BY project;

PostgreSQL 版本

WITH staff_list AS (
    SELECT project, cost, unnest(array[staff_1, staff_2, staff_3]) AS staff
    FROM table
    WHERE unnest(array[staff_1, staff_2, staff_3]) IS NOT NULL
)
SELECT 
    project,
    COUNT(DISTINCT staff) AS num_distinct_staff,
    SUM(DISTINCT cost) AS total_cost
FROM staff_list
GROUP BY project;

内容的提问来源于stack exchange，提问作者Simon.S.A.