SQL UNION ALL查询结果相同部门计数合并实现求助

实现方案 #

你可以通过两种方式实现同部门计数合并：

方式1：基于现有查询外层嵌套聚合 #

将你现有UNION ALL的查询结果作为子查询，外层按部门维度分组后对计数字段求和即可：

SELECT PTW_MRC, Department, SUM(CountofDept) AS CountofDept
FROM (
    Select  b.PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,count(*) as CountofDept
    from U5PERMITAUDIT a inner join R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE
    Where CREATED between '2016-01-01' and '2021-11-10'
    and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC) 
    and PTW_RESP = PTW_RESP-- isnull(@Auditedby,PTW_RESP)
    group by PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null)
    UNION ALL
    Select b.PTW_MRC, DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,count(*) as CountofDept
    from U5PERMITAUDIT a inner join R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE
    Where CREATED between '2016-01-01' and '2021-11-10'
    and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC) 
    and PTW_RESP2 = PTW_RESP2--isnull(@Auditedby,PTW_RESP2)
    group by PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null)
) AS t
GROUP BY PTW_MRC, Department

方式2：更高效的单查询实现（推荐） #

无需UNION ALL两次扫表，直接在一次查询中通过条件计数求和，性能更优：

SELECT  
    b.PTW_MRC,
    DBO.r5o7_o7get_desc('EN','MRC', b.PTW_MRC,NULL, null) as Department,
    SUM(CASE WHEN PTW_RESP = PTW_RESP THEN 1 ELSE 0 END) 
    + SUM(CASE WHEN PTW_RESP2 = PTW_RESP2 THEN 1 ELSE 0 END) AS CountofDept
FROM U5PERMITAUDIT a 
INNER JOIN R5PERMITTOWORK b on a.PTW_CODE = b.PTW_CODE
WHERE CREATED between '2016-01-01' and '2021-11-10'
and PTW_MRC = PTW_MRC--isnull(@Dept,PTW_MRC) 
GROUP BY PTW_MRC,DBO.r5o7_o7get_desc('EN','MRC', PTW_MRC,NULL, null)

注：如果你原SQL里PTW_RESP = PTW_RESP是注释了参数过滤的占位逻辑，替换成实际的参数判断条件后以上方案依然适用。

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