如何将PostgreSQL中含CASE、COUNT、GROUP的SELECT查询转换为DB2语法

原PostgreSQL语句在DB2中无法运行的原因主要有三个：空值判断逻辑错误、不支持GROUP BY直接引用SELECT别名、整数除法精度问题。以下是修正后的两种可行写法：

写法一：重复CASE表达式分组 #

SELECT
    CASE
        WHEN total IS NULL OR total = 0 THEN 'skip'
        WHEN ROUND(CAST(score AS DECIMAL(10,2)) / total * 100, 2) >= 91 THEN '>=91'
        WHEN ROUND(CAST(score AS DECIMAL(10,2)) / total * 100, 2) BETWEEN 80 AND 90 THEN '>=80 & <=90'
        ELSE 'other'
    END AS perc,
    COUNT(*)
FROM customer
GROUP BY
    CASE
        WHEN total IS NULL OR total = 0 THEN 'skip'
        WHEN ROUND(CAST(score AS DECIMAL(10,2)) / total * 100, 2) >= 91 THEN '>=91'
        WHEN ROUND(CAST(score AS DECIMAL(10,2)) / total * 100, 2) BETWEEN 80 AND 90 THEN '>=80 & <=90'
        ELSE 'other'
    END;

写法二：使用CTE简化分组（推荐） #

WITH customer_calc AS (
    SELECT
        CASE
            WHEN total IS NULL OR total = 0 THEN 'skip'
            WHEN ROUND(CAST(score AS DECIMAL(10,2)) / total * 100, 2) >= 91 THEN '>=91'
            WHEN ROUND(CAST(score AS DECIMAL(10,2)) / total * 100, 2) BETWEEN 80 AND 90 THEN '>=80 & <=90'
            ELSE 'other'
        END AS perc
    FROM customer
)
SELECT perc, COUNT(*)
FROM customer_calc
GROUP BY perc;

关键修正说明： #

1. 空值判断修正：原语句中total = 0 or null是无效逻辑，DB2严格遵循SQL标准，必须用total IS NULL来判断空值
2. 类型转换避免整数除法：如果score和total是整数类型，直接除法会截断小数部分，转换为DECIMAL(10,2)后才能得到正确的百分比计算结果
3. GROUP BY规则适配：DB2不允许在GROUP BY子句中直接引用SELECT子句定义的别名，因此要么重复CASE表达式，要么用CTE提前计算出分组字段

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