如何仅用JOIN操作查询2021与2022年同科室就诊的患者

需求：查询2021年和2022年在同一医疗科室（UNIT）就诊的患者，原本使用INTERSECT语句实现，现需仅通过JOIN类操作完成。

原INTERSECT实现代码 #

SELECT P.NAME|| ' ' || P.LASTNAME|| ' ' || P.LASTNAME2 AS PATIENT,  U.UNIT_NAME AS UNIT
FROM ATTENTIONS
JOIN MEDIC M USING (MED_ID)
JOIN UNIT U USING (UNIT_ID)
JOIN PATIENT P USING (PATID)
WHERE EXTRACT(YEAR FROM ATTETIONDATE)=2021
INTERSECT
SELECT P.NAME|| ' ' || P.LASTNAME|| ' ' || P.LASTNAME2 AS PATIENT,  U.UNIT_NAME AS UNIT
FROM ATTENTIONS
JOIN MEDIC M USING (MED_ID)
JOIN UNIT U USING (UNIT_ID)
JOIN PATIENT P USING (PATID)
WHERE EXTRACT(YEAR FROM ATTETIONDATE)=2022
ORDER BY 1;

改用JOIN的实现方案 #

通过将就诊记录表自连接，分别筛选2021和2022年的就诊数据，再通过患者ID和科室ID关联，就能得到符合条件的结果：

SELECT DISTINCT 
       P.NAME || ' ' || P.LASTNAME || ' ' || P.LASTNAME2 AS PATIENT,
       U.UNIT_NAME AS UNIT
FROM ATTENTIONS a2021
JOIN MEDIC m2021 USING (MED_ID)
JOIN UNIT U USING (UNIT_ID)
JOIN PATIENT P USING (PATID)
-- 自连接2022年的就诊记录，关联条件是同一患者、同一科室
JOIN ATTENTIONS a2022 
  ON a2021.PATID = a2022.PATID 
  AND a2021.UNIT_ID = a2022.UNIT_ID
JOIN MEDIC m2022 ON a2022.MED_ID = m2022.MED_ID
WHERE EXTRACT(YEAR FROM a2021.ATTETIONDATE) = 2021
  AND EXTRACT(YEAR FROM a2022.ATTETIONDATE) = 2022
ORDER BY PATIENT;

关键说明 #

• 给ATTENTIONS表起两个别名a2021和a2022，分别对应两年的就诊数据
• 连接条件a2021.PATID = a2022.PATID确保是同一个患者，a2021.UNIT_ID = a2022.UNIT_ID确保是同一科室
• 使用DISTINCT避免同一患者同一科室因多次就诊产生重复结果
• 保留原有的表关联逻辑，确保能正确获取患者姓名和科室名称

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