如何使用ANSI SQL对GROUP BY分组结果行进行合并统计？

你可以通过CASE表达式先对需要合并的COMMISSION_PCT值做映射转换，再做分组聚合即可，完全符合ANSI SQL标准，兼容所有支持标准SQL的数据库。

方法1：子查询写法（可读性更高） #

SELECT 
    mapped_commission_pct AS COMMISSION_PCT,
    COUNT(*) AS `COUNT(*)`
FROM (
    SELECT
        -- 按照需求映射佣金比例
        CASE
            WHEN COMMISSION_PCT = 0.25 THEN 0.20
            WHEN COMMISSION_PCT = 0.35 THEN 0.30
            ELSE COMMISSION_PCT
        END AS mapped_commission_pct
    FROM EMPLOYEES
) AS t
GROUP BY mapped_commission_pct;

方法2：直接分组写法（无需子查询） #

如果你不想写子查询，也可以直接把CASE逻辑同时写在SELECT和GROUP BY子句中，执行效果完全一致：

SELECT
    CASE
        WHEN COMMISSION_PCT = 0.25 THEN 0.20
        WHEN COMMISSION_PCT = 0.35 THEN 0.30
        ELSE COMMISSION_PCT
    END AS COMMISSION_PCT,
    COUNT(*) AS `COUNT(*)`
FROM EMPLOYEES
GROUP BY
    CASE
        WHEN COMMISSION_PCT = 0.25 THEN 0.20
        WHEN COMMISSION_PCT = 0.35 THEN 0.30
        ELSE COMMISSION_PCT
    END;

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