SQL如何基于当前日期查询患者最近过往与未来预约日期

原SQL存在的问题 #

• 缺失分组逻辑：使用MAX/MIN这类聚合函数时，必须指定分组维度，否则会将全表所有预约数据混合计算，多数数据库会直接抛出语法错误
• 聚合规则不符合需求：直接取全表预约的全局最早/最晚值，没有以查询执行时的当前日期为分界，区分过往、未来两个时间区间的最近预约
• 连接方式不匹配：使用INNER JOIN会直接过滤掉没有任何预约记录的患者，无法实现“关联所有患者”的查询目标

正确实现逻辑 #

采用条件聚合的方式，在聚合计算时对预约日期和当前日期做大小判断，分别取时间区间内的最值，搭配按患者分组的规则即可得到预期结果。
通用SQL实现参考：

SELECT 
    p.PatientName,
    -- 计算每位患者最近一次的过往预约：所有早于当前日期的预约中取最大值
    MAX(CASE 
        WHEN a.AppointmentDate < CURRENT_DATE 
        THEN a.AppointmentDate 
    END) AS LastVisit,
    -- 计算每位患者最近一次的未来预约：所有晚于当前日期的预约中取最小值
    MIN(CASE 
        WHEN a.AppointmentDate > CURRENT_DATE 
        THEN a.AppointmentDate 
    END) AS NextVisit
FROM Patient p
-- 左连接保留全量患者，无预约记录的患者对应预约字段返回NULL
LEFT JOIN Appointment a 
    ON p.PatientId = a.PatientId
-- 按患者维度分组，确保统计维度是单患者而非全表
GROUP BY p.PatientId, p.PatientName;

结果验证（匹配你提供的示例数据） #

假设查询执行时的当前日期为2020-11-01：

• 患者的过往预约集合为2020-08-08、2020-10-11，取集合最大值得到2020-10-11，与预期的最近过往预约一致
• 患者的未来预约集合为2020-12-15、2020-12-24，取集合最小值得到2020-12-15，与预期的最近未来预约一致
• 当患者无过往/未来对应类型的预约时，对应统计字段返回NULL，匹配业务逻辑预期

不同数据库的函数适配 #

部分数据库获取当前日期的函数存在差异，可根据实际环境替换：

• MySQL：用CURDATE()替换通用语法中的CURRENT_DATE
• SQL Server：用CAST(GETDATE() AS DATE)替换
• Oracle：用TRUNC(SYSDATE)替换

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