MySQL按年龄区间统计男性患者各类疾病计数的实现方法

你可以通过条件聚合的方式实现三个指标的分别统计，以下是修改后的语句：

基础版（仅返回有数据的年龄区间） #

SELECT 
    t.ranges,
    SUM(CASE WHEN dm = 1 THEN 1 ELSE 0 END) AS dm_count,
    SUM(CASE WHEN htn = 1 THEN 1 ELSE 0 END) AS htn_count,
    SUM(CASE WHEN ckd = 1 THEN 1 ELSE 0 END) AS ckd_count
FROM (
    SELECT 
        age,
        dm,
        htn,
        ckd,
        CASE 
            WHEN age >= 0 AND age < 20 THEN 'under 20'
            WHEN age >= 20 AND age < 30 THEN '20-29'
            WHEN age >= 30 AND age < 40 THEN '30-39'
            WHEN age >= 40 AND age < 50 THEN '40-49'
            WHEN age >= 50 AND age < 60 THEN '50-59'
            WHEN age >= 60 AND age < 70 THEN '60-69'
            WHEN age >= 70 AND age < 80 THEN '70-79'
            ELSE '80+' 
        END AS ranges
    FROM patient_sample 
    WHERE LOWER(sex) = 'm'
) AS t
GROUP BY t.ranges

全量区间版（返回所有8个年龄区间，无数据的指标计数显示为0，完全匹配示例输出） #

如果需要和你给出的示例结果一致，展示所有年龄段区间，哪怕没有符合条件的患者也显示0计数，可以用以下语句：

SELECT 
    r.ranges,
    COALESCE(SUM(CASE WHEN p.dm = 1 THEN 1 ELSE 0 END), 0) AS dm_count,
    COALESCE(SUM(CASE WHEN p.htn = 1 THEN 1 ELSE 0 END), 0) AS htn_count,
    COALESCE(SUM(CASE WHEN p.ckd = 1 THEN 1 ELSE 0 END), 0) AS ckd_count
FROM (
    -- 手动构造所有需要展示的年龄区间
    SELECT 'under 20' AS ranges UNION ALL
    SELECT '20-29' UNION ALL
    SELECT '30-39' UNION ALL
    SELECT '40-49' UNION ALL
    SELECT '50-59' UNION ALL
    SELECT '60-69' UNION ALL
    SELECT '70-79' UNION ALL
    SELECT '80+'
) AS r
LEFT JOIN (
    SELECT 
        age,
        dm,
        htn,
        ckd,
        CASE 
            WHEN age >= 0 AND age < 20 THEN 'under 20'
            WHEN age >= 20 AND age < 30 THEN '20-29'
            WHEN age >= 30 AND age < 40 THEN '30-39'
            WHEN age >= 40 AND age < 50 THEN '40-49'
            WHEN age >= 50 AND age < 60 THEN '50-59'
            WHEN age >= 60 AND age < 70 THEN '60-69'
            WHEN age >= 70 AND age < 80 THEN '70-79'
            ELSE '80+' 
        END AS ranges
    FROM patient_sample 
    WHERE LOWER(sex) = 'm'
) AS p ON r.ranges = p.ranges
GROUP BY r.ranges

主要修改点说明 #

• 内层查询新增dm/htn/ckd三个字段的查询，供外层聚合计算使用
• 外层将原来的count(*)替换为三个条件求和语句，仅统计对应指标值为1的行数
• 修正了原SQL中age字段用字符串比较的错误，去掉了数值的单引号，避免非预期的比较结果
• 新增LOWER(sex)逻辑，兼容sex字段的大小写差异
• 全量区间版通过构造全量区间临时表左关联业务数据，配合COALESCE函数把空值转为0，完全匹配你需要的输出格式。

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