如何更优实现SQL条件分组求和？现有方案求改进

原始数据 #

目标汇总结果 #

已实现的两种方法 #

方法1：使用UNION #

注：原SQL存在逻辑问题，修正后才能得到预期结果，需将<=2020的年份统一命名，并对>2020的年份单独分组：

SELECT '2020 and before' AS Year, SUM(Amount) AS Amount
FROM TABLE_A
WHERE Year <= 2020
UNION ALL
SELECT CAST(Year AS VARCHAR) AS Year, SUM(Amount) AS Amount
FROM TABLE_A
WHERE Year > 2020
GROUP BY Year

方法2：子查询+CASE分组 #

SELECT YR, SUM(Amount) AS Amount
FROM (
    SELECT 
        CASE WHEN Year <= 2020 THEN '2020 and before' ELSE CAST(Year AS VARCHAR) END AS YR,
        Amount
    FROM TABLE_A
) A
GROUP BY YR

更优实现方案 #

第二种方法可以进一步优化，去掉子查询，直接在SELECT和GROUP BY中复用CASE逻辑，避免创建临时中间表，执行效率更高：

SELECT
    CASE
        WHEN Year <= 2020 THEN '2020 and before'
        ELSE CAST(Year AS VARCHAR(4))  -- 不同数据库语法略有差异：MySQL用CAST(Year AS CHAR)，PostgreSQL用Year::TEXT
    END AS Year,
    SUM(Amount) AS Amount
FROM TABLE_A
GROUP BY
    CASE
        WHEN Year <= 2020 THEN '2020 and before'
        ELSE CAST(Year AS VARCHAR(4))
    END

方案优势对比 #

• 原UNION方法需要两次扫描表，数据量较大时性能损耗明显；若误用UNION而非UNION ALL，还会额外触发去重操作，完全没必要。
• 优化后的CASE分组方法仅需一次表扫描，分组逻辑直接在聚合阶段处理，执行计划更简洁，性能更优。

如果使用支持GROUP BY别名的数据库（如MySQL、PostgreSQL），还能进一步简化写法：

-- MySQL/PostgreSQL 简化版
SELECT
    CASE
        WHEN Year <= 2020 THEN '2020 and before'
        ELSE CAST(Year AS VARCHAR(4))
    END AS Year,
    SUM(Amount) AS Amount
FROM TABLE_A
GROUP BY Year

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