如何对CASE表达式的返回结果执行GROUP BY分组操作

SQL的逻辑执行顺序为 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY，你在SELECT阶段才为CASE表达式定义别名Ticket，而GROUP BY执行优先级早于SELECT，此时别名还未生成，因此数据库会提示Ticket不是有效字段。

另外你提供的原SQL还存在语法疏漏：CAST ( SUM(AMT_1/100) AS DECIMAL(10,2) ) 与 Count(*) 两个返回字段之间缺少逗号，也会触发语法报错。

方案1：将完整CASE表达式写入GROUP BY（全数据库兼容） #

该方案兼容性最好，支持所有主流SQL数据库，无需调整语句结构：

SELECT 
CASE WHEN ( amt_1 >= 10000000                             ) THEN  'R100000'
     WHEN ( amt_1 <   9000000 and amt_1 >=  8000000       ) THEN  'R090000'
     WHEN ( amt_1 <   8000000 and amt_1 >=  7000000       ) THEN  'R080000'
     WHEN ( amt_1 <   7000000 and amt_1 >=  6000000       ) THEN  'R070000'
     WHEN ( amt_1 <   6000000 and amt_1 >=  5000000       ) THEN  'R060000'
     WHEN ( amt_1 <   5000000 and amt_1 >=  4000000       ) THEN  'R050000'
     WHEN ( amt_1 <   4000000 and amt_1 >=  3000000       ) THEN  'R040000'
     WHEN ( amt_1 <   3000000 and amt_1 >=  2000000       ) THEN  'R030000'
     WHEN ( amt_1 <   2000000 and amt_1 >=  1000000       ) THEN  'R020000'
     WHEN ( amt_1 <   1000000 and amt_1 >=  500000        ) THEN  'R010000'
     WHEN ( amt_1 <    500000 and amt_1 >=  100000        ) THEN  'R005000'
     WHEN ( amt_1 <    100000                             ) THEN  'R001000'             
END  as Ticket,    
CAST ( SUM(AMT_1/100) AS DECIMAL(10,2) ),
Count(*)     
FROM BASE24.PTLF 
GROUP BY 
CASE WHEN ( amt_1 >= 10000000                             ) THEN  'R100000'
     WHEN ( amt_1 <   9000000 and amt_1 >=  8000000       ) THEN  'R090000'
     WHEN ( amt_1 <   8000000 and amt_1 >=  7000000       ) THEN  'R080000'
     WHEN ( amt_1 <   7000000 and amt_1 >=  6000000       ) THEN  'R070000'
     WHEN ( amt_1 <   6000000 and amt_1 >=  5000000       ) THEN  'R060000'
     WHEN ( amt_1 <   5000000 and amt_1 >=  4000000       ) THEN  'R050000'
     WHEN ( amt_1 <   4000000 and amt_1 >=  3000000       ) THEN  'R040000'
     WHEN ( amt_1 <   3000000 and amt_1 >=  2000000       ) THEN  'R030000'
     WHEN ( amt_1 <   2000000 and amt_1 >=  1000000       ) THEN  'R020000'
     WHEN ( amt_1 <   1000000 and amt_1 >=  500000        ) THEN  'R010000'
     WHEN ( amt_1 <    500000 and amt_1 >=  100000        ) THEN  'R005000'
     WHEN ( amt_1 <    100000                             ) THEN  'R001000'             
END

方案2：用CTE预计算Ticket字段（可读性更高） #

如果不想重复书写CASE逻辑，可以用CTE先计算好分组字段，外层再做聚合操作：

WITH ticket_data AS (
    SELECT 
        CASE WHEN ( amt_1 >= 10000000                             ) THEN  'R100000'
             WHEN ( amt_1 <   9000000 and amt_1 >=  8000000       ) THEN  'R090000'
             WHEN ( amt_1 <   8000000 and amt_1 >=  7000000       ) THEN  'R080000'
             WHEN ( amt_1 <   7000000 and amt_1 >=  6000000       ) THEN  'R070000'
             WHEN ( amt_1 <   6000000 and amt_1 >=  5000000       ) THEN  'R060000'
             WHEN ( amt_1 <   5000000 and amt_1 >=  4000000       ) THEN  'R050000'
             WHEN ( amt_1 <   4000000 and amt_1 >=  3000000       ) THEN  'R040000'
             WHEN ( amt_1 <   3000000 and amt_1 >=  2000000       ) THEN  'R030000'
             WHEN ( amt_1 <   2000000 and amt_1 >=  1000000       ) THEN  'R020000'
             WHEN ( amt_1 <   1000000 and amt_1 >=  500000        ) THEN  'R010000'
             WHEN ( amt_1 <    500000 and amt_1 >=  100000        ) THEN  'R005000'
             WHEN ( amt_1 <    100000                             ) THEN  'R001000'             
        END  as Ticket,
        AMT_1
    FROM BASE24.PTLF
)
SELECT 
    Ticket,
    CAST(SUM(AMT_1/100) AS DECIMAL(10,2)),
    Count(*)
FROM ticket_data
GROUP BY Ticket

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