如何简化按月汇总部门faltas的Oracle SQL查询？

针对你用多次UNION ALL重复代码来汇总部门近N个月faltas并生成合计报表的问题，可以通过以下几种简洁写法实现相同效果，大幅降低维护成本：

方案1：条件聚合+ROLLUP（月份作为列展示） #

如果需要把每个月份作为单独的列、部门作为行，同时自动生成"Total Geral"汇总行，用这种写法：

SELECT 
    CASE WHEN GROUPING(dept) = 1 THEN 'Total Geral' ELSE dept END AS departamento,
    -- 近3个月的统计，13个月的话只需修改ADD_MONTHS的偏移量为-12
    SUM(CASE WHEN TRUNC(data, 'MM') = ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) THEN faltas ELSE 0 END) AS mes_3,
    SUM(CASE WHEN TRUNC(data, 'MM') = ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) THEN faltas ELSE 0 END) AS mes_2,
    SUM(CASE WHEN TRUNC(data, 'MM') = TRUNC(SYSDATE, 'MM') THEN faltas ELSE 0 END) AS mes_1,
    SUM(faltas) AS total_por_departamento
FROM sua_tabela
-- 过滤近3个月数据，13个月则改成ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
WHERE data BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) AND LAST_DAY(TRUNC(SYSDATE, 'MM'))
GROUP BY ROLLUP(dept);

优势： #

• 只需要写一次主查询逻辑，修改月份范围仅需调整ADD_MONTHS的偏移参数
• ROLLUP自动生成全局汇总行，无需额外写UNION ALL拼接合计

方案2：GROUPING SETS（月份作为行展示） #

如果报表需要每个部门每个月一行，最后追加全局合计，用GROUPING SETS替代多次UNION ALL：

SELECT 
    CASE WHEN GROUPING(dept) = 1 THEN 'Total Geral' ELSE dept END AS departamento,
    CASE WHEN GROUPING(TO_CHAR(data, 'YYYY-MM')) = 1 THEN 'Todos os meses' ELSE TO_CHAR(data, 'YYYY-MM') END AS mes,
    SUM(faltas) AS total_faltas
FROM sua_tabela
WHERE data BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) AND LAST_DAY(TRUNC(SYSDATE, 'MM'))
-- 指定分组组合：(部门+月份)的明细、全局合计
GROUP BY GROUPING SETS ((dept, TO_CHAR(data, 'YYYY-MM')), ());

优势： #

• 一次分组即可同时生成明细行和合计行，完全避免重复代码
• 扩展月份范围只需修改WHERE条件中的日期偏移

方案3：CTE+单次UNION ALL（兼容旧版本Oracle） #

如果使用的Oracle版本不支持GROUPING SETS/ROLLUP（10g及以前），可以用CTE先统计明细，再追加合计：

WITH dados_mensais AS (
    SELECT 
        dept AS departamento,
        TO_CHAR(data, 'YYYY-MM') AS mes,
        SUM(faltas) AS total_faltas
    FROM sua_tabela
    WHERE data BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) AND LAST_DAY(TRUNC(SYSDATE, 'MM'))
    GROUP BY dept, TO_CHAR(data, 'YYYY-MM')
)
SELECT departamento, mes, total_faltas FROM dados_mensais
UNION ALL
SELECT 'Total Geral', 'Todos os meses', SUM(total_faltas) FROM dados_mensais
ORDER BY departamento, mes;

优势： #

• 明细逻辑只写一次，合计直接复用CTE结果，比多次UNION ALL简洁得多

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