如何在指定SQL查询中按age_range计算百分比及复用列别名

原始查询语句 #

SELECT c.chronicCondition,count(distinct(p.code)),
   CASE
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
        ELSE 'Other'
    END AS age_range
FROM Patient p
   LEFT JOIN classification c ON p.code = c.patientCode
group by c.chronicCondition
ORDER BY age_range

问题1：如何基于age_range计算百分比？ #

要计算每个age_range分组下的患者数占总患者数的百分比，核心是先获取全局总患者数，再用分组计数除以总数得到比例，以下是两种可行方案：

方案1：使用窗口函数（推荐，支持MySQL 8+、PostgreSQL等） #

通过COUNT() OVER()直接获取全局唯一患者总数，结合分组计数计算百分比：

SELECT 
    c.chronicCondition,
    COUNT(DISTINCT p.code) AS patient_count,
    CASE
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
        ELSE 'Other'
    END AS age_range,
    -- 计算百分比并保留两位小数
    ROUND(
        (COUNT(DISTINCT p.code) * 100.0) / COUNT(DISTINCT p.code) OVER(),
        2
    ) AS percentage
FROM Patient p
LEFT JOIN classification c ON p.code = c.patientCode
GROUP BY c.chronicCondition, age_range  -- 必须将age_range加入分组，保证结果对应正确
ORDER BY age_range

方案2：使用子查询（兼容旧版数据库） #

先通过子查询获取总患者数，再关联分组结果计算百分比：

SELECT 
    sub.chronicCondition,
    sub.patient_count,
    sub.age_range,
    ROUND((sub.patient_count * 100.0) / total.total_patients, 2) AS percentage
FROM (
    SELECT 
        c.chronicCondition,
        COUNT(DISTINCT p.code) AS patient_count,
        CASE
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
            ELSE 'Other'
        END AS age_range
    FROM Patient p
    LEFT JOIN classification c ON p.code = c.patientCode
    GROUP BY c.chronicCondition, age_range
) AS sub
CROSS JOIN (
    SELECT COUNT(DISTINCT code) AS total_patients FROM Patient
) AS total
ORDER BY sub.age_range

问题2：如何在同一SQL查询中使用已定义的列别名？ #

SQL执行顺序是FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，因此SELECT中定义的别名无法直接在WHERE、GROUP BY、HAVING中使用，只能在ORDER BY中用。要在其他子句中使用别名，有三种常用方法：

方法1：使用CTE/子查询封装别名逻辑 #

把定义别名的逻辑放到CTE（公共表表达式）或子查询中，外层查询即可直接调用别名：

-- 以CTE为例（支持MySQL 8+、PostgreSQL、SQL Server等）
WITH patient_age_groups AS (
    SELECT 
        c.chronicCondition,
        p.code,
        CASE
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
            WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
            ELSE 'Other'
        END AS age_range
    FROM Patient p
    LEFT JOIN classification c ON p.code = c.patientCode
)
SELECT 
    chronicCondition,
    COUNT(DISTINCT code) AS patient_count,
    age_range
FROM patient_age_groups
WHERE age_range != 'Other'  -- 直接使用别名过滤
GROUP BY chronicCondition, age_range
ORDER BY age_range

方法2：重复别名对应的表达式 #

如果不想用子查询，可直接重复编写别名对应的逻辑（虽然冗余但简单直接）：

SELECT 
    c.chronicCondition,
    COUNT(DISTINCT p.code) AS patient_count,
    CASE
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
        ELSE 'Other'
    END AS age_range
FROM Patient p
LEFT JOIN classification c ON p.code = c.patientCode
-- 重复CASE表达式做过滤
WHERE CASE
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
        WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
        ELSE 'Other'
    END != 'Other'
GROUP BY c.chronicCondition, 
         -- 分组时重复CASE表达式
         CASE
             WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 30 AND 60 THEN '30-60'
             WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 60 AND 75 THEN '60-75'
             WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 75 AND 90 THEN '75-90'
             WHEN FLOOR(DATEDIFF(CURDATE(), p.dob) / 365.25) BETWEEN 90 AND 100 THEN '90-100'
             ELSE 'Other'
         END
ORDER BY age_range

方法3：使用数据库特定语法 #

部分数据库（如PostgreSQL、Oracle 12c+）支持在GROUP BY中直接使用别名，但这不属于SQL标准，跨库兼容性差，不推荐使用。

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