如何将UNION查询结果中的每组合并为单行？

示例数据 #

原分组统计查询 #

SELECT group
     , unit
     , department
     , NULL as team
     , COUNT(*) AS count
  FROM table
 WHERE group='g1' 
   AND unit='u1' 
   AND department='d1'
GROUP
    BY group
     , unit
     , department
UNION ALL
SELECT group
     , unit
     , department
     , team
     , COUNT(*) AS count
  FROM table
 WHERE group='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
GROUP
    BY group
     , unit
     , department
     , team

原查询返回结果 #

期望的合并后结果 #

解决方案：使用条件聚合实现单行合并 #

核心思路是用条件聚合替代原查询中按状态拆分的逻辑，通过CASE语句筛选指定状态后计数，直接将同一分组的两种状态统计结果输出到单行。

针对示例的优化查询

SELECT group
     , unit
     , department
     , NULL as team
     , COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count
     , COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
  FROM table
 WHERE group='g1' 
   AND unit='u1' 
   AND department='d1'
GROUP BY group, unit, department
UNION ALL
SELECT group
     , unit
     , department
     , team
     , COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count
     , COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
  FROM table
 WHERE group='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
GROUP BY group, unit, department, team

动态构建查询的示例

若需根据输入数组（如["g1", "u1", "d1"]、["g25", "u54", "d70", "t88"]、["g3", "u6"]）动态生成查询，需按数组包含的层级字段构建SELECT、WHERE和GROUP BY子句，示例如下：

SELECT group_
     , unit
     , department
     , NULL as team
     , COUNT(CASE WHEN status='active' THEN 1 END) AS active_count
     , COUNT(CASE WHEN status='inactive' THEN 1 END) AS inactive_count
  FROM my_table
 WHERE group_='g1' 
   AND unit='u1' 
   AND department='d1'
GROUP BY group_, unit, department
UNION ALL
SELECT group_
     , unit
     , department
     , team
     , COUNT(CASE WHEN status='active' THEN 1 END) AS active_count
     , COUNT(CASE WHEN status='inactive' THEN 1 END) AS inactive_count
  FROM my_table
 WHERE group_='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
GROUP BY group_, unit, department, team
UNION ALL
SELECT group_
     , unit
     , NULL as department
     , NULL as team
     , COUNT(CASE WHEN status='active' THEN 1 END) AS active_count
     , COUNT(CASE WHEN status='inactive' THEN 1 END) AS inactive_count
  FROM my_table
 WHERE group_='g3' 
   AND unit='u6' 
GROUP BY group_, unit

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