Oracle GROUP BY使用问题：UNION ALL查询结果未按SERVER字段分组如何解决

问题原因 #

• GROUP BY仅对UNION ALL的最后一段查询生效，不会处理UNION ALL拼接后的完整结果集。UNION ALL本身仅做结果行的简单拼接，不会合并同SERVER的行，因此手动硬编码的两条SERVER为WMSALPRO2、WMSALPRO1的0值记录，会和后续统计出的同SERVER计数记录同时返回，最终出现同一个SERVER对应两条数据的现象。

解决方案 #

将整个UNION ALL的结果作为子查询，在外层统一按SERVER分组求和即可得到合并后的正确结果，该方案改动最小、完全兼容原有逻辑，修改后的SQL如下：

SELECT SERVER, SUM("TOTALSESIONES") AS "TOTALSESIONES"
FROM (
    SELECT 'WMSALPRO2' AS SERVER,
           0 AS "TOTALSESIONES"
    FROM   DUAL
    UNION ALL
    SELECT 'WMSALPRO1' AS SERVER,
           0 AS "TOTALSESIONES"
    FROM   DUAL
    UNION ALL 
    SELECT SERVER AS SERVER,
           COUNT(*) AS "TOTALSESIONES" 
    FROM   (
      SELECT b.sid,
             b.username,
             b.module,
             TRUNC(a.seconds_in_wait/60,2) AS MINUTOS_ESPERA,
             a.event,
             c.object_name,
             b.sql_id,
             d.instance_name AS SERVER 
      FROM   gv$session_wait a,
             gv$session b,
             dba_objects c,
             gv$instance d 
      WHERE  a.event NOT IN (
               'Streams AQ: waiting for messages in the queue',
               'smon timer',
               'pmon timer',
               'rdbms ipc message',
               'SQL*Net message from client',
               'pipe get',
               'null event',
               'SQL*Net message to client'
             ) 
      AND    b.SID = a.SID 
      AND    d.inst_id=a.inst_id 
      AND    username IS NOT NULL 
      AND    C.OBJECT_ID(+) = row_wait_obj# 
      AND    B.STATUS ='ACTIVE'
    )
    GROUP BY SERVER
) t
GROUP BY SERVER;

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