如何实现SQL分组求和并合并同国籍数量生成统计列表？

你的问题核心是没有先对同国籍的数量进行分组求和，直接在原表分组后使用STRING_AGG会保留每条原始记录的条目，导致同国籍重复显示。需要先通过子查询/CTE合并同国籍的数量，再进行字符串聚合。

修改后的SQL代码 #

WITH NationalityTotals AS (
    -- 第一步：先按部门、日期、班次、国籍分组，计算每个国籍的总数量
    SELECT 
        DepartmentName,
        Date,
        Turn,
        Nationality,
        SUM(CASE
            WHEN Turn = 'Morning' THEN MorningQuantity
            WHEN Turn = 'Afternoon' THEN AfternoonQuantity
            WHEN Turn = 'Night' THEN NightQuantity
        END) AS NatTotal
    FROM DummyTable
    GROUP BY DepartmentName, Date, Turn, Nationality
)
-- 第二步：基于已合并的国籍数量，再按部门、日期、班次聚合
SELECT 
    DepartmentName,
    Date,
    Turn,
    SUM(NatTotal) AS Quantity,
    -- 拼接合并后的国籍数量字符串，可按国籍排序保证输出一致
    STRING_AGG(CAST(NatTotal AS VARCHAR(3)) + ' ' + Nationality, ', ') 
        WITHIN GROUP (ORDER BY Nationality) AS OtherNationalities
FROM NationalityTotals
GROUP BY DepartmentName, Date, Turn

关键逻辑说明 #

1. CTE层NationalityTotals：先把同一部门、日期、班次下的同国籍数量求和，生成每个国籍对应的总数量记录，避免后续聚合时重复。
2. 主查询层：基于CTE的结果，再次按DepartmentName, Date, Turn分组，此时STRING_AGG处理的是每个国籍的总数量，自然会生成4 ES, 6 FR这类合并后的格式。
3. WITHIN GROUP (ORDER BY Nationality)可选，用于保证输出的国籍顺序固定，避免每次查询结果顺序不一致。

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