如何编写SQL脚本查询近6个月未接受牙科洗牙的患者

需求背景 #

现有两张业务数据表：

• Patients 患者表：包含字段 patient_id（患者ID）、patient_name（患者姓名）
• Visits 就诊/预约表：包含字段 v_id（记录ID）、patient_id（关联患者ID）、reason_visit（就诊/预约原因）、date_of_visit（就诊/预约日期）

需返回符合以下规则的患者ID与姓名：

1. 纳入范围：从未在本院有洗牙记录，或上次洗牙时间距当前超过6个月的患者
2. 排除范围：符合上述纳入条件，但已经预约未来洗牙服务的患者

实现逻辑说明 #

• 第一步：统计每个患者的最近一次历史洗牙记录时间，筛选出最近洗牙时间早于6个月前，或无任何洗牙记录的患者
• 第二步：统计所有预约了未来洗牙服务的患者名单，从上一步结果中排除该部分人群
• 第三步：关联患者表匹配对应患者ID和姓名，去重后输出结果

参考SQL实现（MySQL语法） #

SELECT DISTINCT p.patient_id, p.patient_name
FROM Patients p
-- 关联未来洗牙预约记录，用于排除对应患者
LEFT JOIN Visits v_future 
  ON p.patient_id = v_future.patient_id
  AND v_future.reason_visit = '洗牙'
  AND v_future.date_of_visit > CURDATE()
-- 关联历史洗牙记录，用于判断上次洗牙时间
LEFT JOIN Visits v_history
  ON p.patient_id = v_history.patient_id
  AND v_history.reason_visit = '洗牙'
  AND v_history.date_of_visit <= CURDATE()
WHERE 
  v_future.v_id IS NULL -- 过滤无未来洗牙预约的患者
  AND (
    v_history.v_id IS NULL -- 保留从未有洗牙记录的患者
    OR MAX(v_history.date_of_visit) < DATE_SUB(CURDATE(), INTERVAL 6 MONTH) -- 保留上次洗牙超过6个月的患者
  )
GROUP BY p.patient_id, p.patient_name;

适配说明 #

• 实际使用时请根据自身数据库类型调整日期函数，例如Oracle场景用SYSDATE、ADD_MONTHS替换上述日期相关函数即可
• 请确认reason_visit字段中“洗牙”对应的业务存储值，和SQL里的匹配值保持一致，避免匹配遗漏

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