编写SQL Server查询：按部门分组展示去重血型及员工总数

要解决你遇到的问题——生成每个部门去重后的血型逗号分隔列表，同时统计部门员工总数，核心是先对血型去重再做聚合。直接用STRING_AGG会重复输出同一血型，所以得先过滤重复值再拼接。

方案一：使用CTE（清晰易读） #

WITH DeptUniqueBloods AS (
    -- 先获取每个部门的所有唯一血型
    SELECT DISTINCT
        ed.department,
        eb.blood_type
    FROM empdeps ed
    JOIN empbloods eb ON ed.emp_id = eb.emp_id
)
SELECT
    department AS 部门名称,
    -- 拼接去重后的血型，按血型排序让列表更规整
    STRING_AGG(blood_type, ', ') WITHIN GROUP (ORDER BY blood_type) AS 去重血型列表,
    -- 统计对应部门的员工总数
    (SELECT COUNT(emp_id) FROM empdeps WHERE department = dub.department) AS 员工总数
FROM DeptUniqueBloods dub
GROUP BY department;

方案二：子查询写法 #

如果不想用CTE，也可以直接嵌套子查询实现：

SELECT
    department AS 部门名称,
    STRING_AGG(blood_type, ', ') WITHIN GROUP (ORDER BY blood_type) AS 去重血型列表,
    emp_count AS 员工总数
FROM (
    SELECT DISTINCT
        ed.department,
        eb.blood_type,
        -- 提前计算每个部门的员工数
        (SELECT COUNT(emp_id) FROM empdeps WHERE department = ed.department) AS emp_count
    FROM empdeps ed
    JOIN empbloods eb ON ed.emp_id = eb.emp_id
) AS DeptBloods
GROUP BY department, emp_count;

特殊场景处理 #

如果你的empdeps表中存在同一员工归属多个部门的情况，统计员工总数时要改用COUNT(DISTINCT emp_id)，避免重复计数：

(SELECT COUNT(DISTINCT emp_id) FROM empdeps WHERE department = dub.department) AS 员工总数

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