如何在SQL中按患者分组筛选Diagnosed=1时对应前3天的历史数据

你之前的SQL仅筛选了Diagnosed=1的单条记录本身，没有关联同一患者的其他历史记录，也没有单独提取每个患者的确诊时间节点，因此无法拉取符合要求的3天内全量数据。
核心实现思路：先通过子查询/CTE提取每个患者的确诊时间，再按患者ID关联全量就诊记录，过滤时间范围即可。

写法1：CTE（适用于PostgreSQL、MySQL 8.0+、SQL Server等支持CTE的数据库） #

WITH patient_diagnosis AS (
    -- 提取每位患者的确诊日期，若存在多条确诊记录可加MIN(ReportDate)取最早确诊时间
    SELECT 
        PatientID,
        ReportDate AS diagnose_date
    FROM Patients
    WHERE Diagnosed = 1
    -- 若允许多条确诊记录，加上GROUP BY PatientID和聚合函数即可
    -- GROUP BY PatientID
)
SELECT p.*
FROM Patients p
INNER JOIN patient_diagnosis pd 
    ON p.PatientID = pd.PatientID
WHERE 
    -- 筛选确诊日期往前3天内的记录，可根据需求调整时间开闭区间
    p.ReportDate >= pd.diagnose_date - INTERVAL '3' day
    AND p.ReportDate <= pd.diagnose_date;

写法2：子查询（兼容低版本数据库） #

SELECT p.*
FROM Patients p
INNER JOIN (
    SELECT 
        PatientID,
        MIN(ReportDate) AS diagnose_date
    FROM Patients
    WHERE Diagnosed = 1
    GROUP BY PatientID
) pd ON p.PatientID = pd.PatientID
WHERE 
    p.ReportDate >= pd.diagnose_date - INTERVAL '3' day
    AND p.ReportDate <= pd.diagnose_date;

注意事项 #

• 若使用MySQL数据库，时间计算语法需调整为DATE_SUB(pd.diagnose_date, INTERVAL 3 DAY)
• 若业务要求不需要包含确诊当天的记录，删除p.ReportDate <= pd.diagnose_date条件即可
• 若同一患者存在多条确诊记录，可根据需求调整聚合逻辑，比如取最晚确诊时间就用MAX(ReportDate)

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