SQL 查询 : how to select records, 但如果存在父记录，请选择它的最新子记录

Question

我有一个复杂的查询需要弄清楚，但我不太精通编写查询和子查询。

问题:我需要检索唯一的患者记录，但如果记录具有非空 master_patient_id，我需要子查询或加入该 master_patient 表并查询最近的(created_at desc 限制1) 该 master_patient 的 child 患者。

这样做的原因是，如果他们被重新接纳到同一家机构，我们的系统将为同一名患者创建新的患者记录。在为给定患者创建第二条记录时，我们还创建了一个 master_patient 记录以将这 2 条患者记录与其相关联，以便系统可以知道它们是同一名患者。

现在，我需要显示非重复患者的列表。所以我需要一个查询，从患者记录中获取患者，但查询 master_patient 表并只检索与其 master_patient 关联的最新患者。

Patient Table 有:id, name, master_patient_id并且患者属于 master_patient 但不需要在场。

Master Patient 表只有一个 id 和 has_many patients。

期望的结果: 应该是唯一的患者记录，但是找出患者之间是否唯一的唯一方法是查询 master_patient 表以​​查看是否有任何患者属于它，然后只检索最新患者(master_patient 的 child )。

我不能基于 master_patient 进行查询，因为只有 1 条记录的患者不存在这些查询。我应该使用某种类型的连接或子查询吗？

更新:感谢@τεκ，我能够调整他的查询以在 Postgres 中工作:

更新 2:对查询进行了 1 次细微调整，使其更短并更正了返回的空 ID:

SELECT MAX(patients.id) as id, * 
FROM "patients" 
JOIN (
   SELECT MAX(created_at) AS created_at, 
       patient_master_id, 
       COALESCE(patient_master_id, id) pm_id 
   FROM patients 
   GROUP BY patient_master_id, 
       COALESCE(patient_master_id, id)
) s 
ON (s.pm_id = patients.id or s.patient_master_id = patients.patient_master_id) 
AND s.created_at = patients.created_at 
GROUP BY patients.id, s.created_at, s.patient_master_id, s.pm_id

Answer 1

select max(id) as id from patient p 
join (select 
  max(created_at) as created_at, 
  master_patient_id, 
  case when master_patient_id is null then id else null end as id
  from patient
  group by master_patient_id, case when master_patient_id is null then id else null end
) s on (s.id = p.id or s.master_patient_id = p.master_patient_id) and s.created_at = p.created_at

可能有一种更简单的、特定于 postgres 的方法，但我不知道 postgres。在 T-SQL 中，它是交叉应用。