使用HAVING子句筛选：统计年检查超2次患者的年度检查数据

问题原因 #

你之前的SQL返回单个患者记录，是因为同时按year(procedure_start)和patient_key分组，最终分组粒度是「每个患者每年」，而非「每年」。要实现按年份汇总，需先筛选出年度内检查次数超过2次的患者，再基于这些患者的记录做年度统计。

方法一：子查询关联实现 #

SELECT 
    COUNT(h.exam_key) AS TOTAL_EXAMS,
    COUNT(DISTINCT h.patient_key) AS TOTAL_PATIENTS,
    YEAR(h.procedure_start) AS EXAM_YEAR
FROM hospitals h
INNER JOIN (
    -- 筛选年度内检查次数>2的患者及对应年份
    SELECT 
        patient_key,
        YEAR(procedure_start) AS exam_year
    FROM hospitals
    GROUP BY patient_key, YEAR(procedure_start)
    HAVING COUNT(exam_key) > 2
) qualified_patients 
ON h.patient_key = qualified_patients.patient_key 
AND YEAR(h.procedure_start) = qualified_patients.exam_year
GROUP BY YEAR(h.procedure_start)
ORDER BY EXAM_YEAR;

方法二：CTE（公共表表达式）实现 #

CTE写法更直观，可读性更强：

WITH qualified_patients AS (
    -- 定义临时数据集：年度内检查次数>2的患者
    SELECT 
        patient_key,
        YEAR(procedure_start) AS exam_year
    FROM hospitals
    GROUP BY patient_key, YEAR(procedure_start)
    HAVING COUNT(exam_key) > 2
)
SELECT 
    COUNT(h.exam_key) AS TOTAL_EXAMS,
    COUNT(DISTINCT h.patient_key) AS TOTAL_PATIENTS,
    YEAR(h.procedure_start) AS EXAM_YEAR
FROM hospitals h
JOIN qualified_patients qp 
    ON h.patient_key = qp.patient_key 
    AND YEAR(h.procedure_start) = qp.exam_year
GROUP BY YEAR(h.procedure_start)
ORDER BY EXAM_YEAR;

逻辑说明 #

1. 子查询/CTE阶段：按「患者+年份」分组，筛选出该年度内检查次数超2次的患者，得到包含patient_key和对应年度的数据集。
2. 主查询阶段：关联原表与上述数据集，仅保留符合条件的患者记录，再按年份分组统计总检查数和患者数。

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