获取所有子记录的记录

Question

我有一张名为medicine的表格，以及一个相关的子表格叫做Medication_symptoms。 Medicine

MediId, Name
1,      MedA
2,      MedB
3,      MedC

药物症状。

MedSympId, Medicine (MedId),     Symptom (symptomId)
1,         MedA (1),             Symptom A (1)
2,         MedA (1),             Symptom B (2)
3,         MedB (2),             Symptom B (2)
4,         MedB (2),             Symptom C (3)
5,         MedC (3),             Symptom D

我有另一个名为“病人”和“病人症状”的表格。 病人

PatientId, Name
1,         Patient A
2,         Patient B
3,         Patient C
4,         Patient D

患者症状

PatientSymptomId, PatientId,     SymptomId
1,                Patient A(1),  Symptom A (1)
2,                Patient A(1),  Symptom B (2)
3,                Patient B(2),  Symptom B (2)
4,                Patient B(2),  Symptom D (4)
5,                Patient D(4),  Symptom D (4)

根据上述信息，我需要获得与患者所有症状匹配的药物：（我将逐个患者地提取信息）

Patient A - Med A (as he has symptom a and b and Med is for symptom A and B)
Patient B - None! (as he has symptoms b and d and there is no medicine for symptoms B and D)
Patient D - Med C (as med C is for symptom D only and Patient D has only symptom D)

注意：症状是一个单独的表格：

症状

Symptom Id, Name
1,          Symptom A
2,          Symptom B
3,          Symptom C
4,          Symptom D
5,          Symptom E

这样的查询被称为什么？

注意：这个例子是我编造的。在我的工作中，我有一条记录A，带有一组属性（其中属性存储为记录行对Record A）。我需要将该记录A与另一条记录C匹配，该记录C具有与A完全相同的属性集。（有意义吗？）

您可以使用http://pastebin.com/kaqdtHf3中的脚本创建表和一些示例数据。

- Raj Rao

---

请在您的示例数据中包括列名。 - Felix Pamittan

你介意解释一下你是如何确定病人的药物治疗逻辑的吗？例如，我不明白为什么病人B的结果是无！。 - sstan

这个查询叫什么？从关系代数的角度来看，你想要的是“除法”（你将患者拥有的症状集合除以药物治疗的症状集合，以获得可以治疗所有症状的药物）。Celko在这个问题上有一篇很好的文章，并提出了一些不同的解决方案：Divided We Stand: The SQL of Relational Division。 - jpw

@jpw，关于关系除法的文章 - 太棒了！谢谢！ - Raj Rao

我已经发布了一个用于创建数据库和数据的脚本，网址是 http://pastebin.com/kaqdtHf3 - Raj Rao

Answer 1

你可以将 not exists 和 full join … null 结合起来，选择所有没有治疗方法的药品，因此该药品具有所有治疗方法，这是一个双重否定。

select * from medicine m
where not exists (
    select 1 from patient_symptom ps 
    full join medication_symptoms ms on ps.SymptomId = ms.SymptomId
          and ps.PatientId = :myPatientIdHere
          and ms.MedId = m.MedId
    where (ms.SymptomId is null or ps.symptomId is null)
)

使用条件聚合的另一种方法是排除不治疗患者症状的任何药物。

select ms.MedId
from patient_symptom ps
join medication_symptoms ms on ps.SymptomId = ms.SymptomId
where ps.patientId = :myPatientIdHere
group by ms.MedId, ps.patientId
having count(ms.symptomId) = (select count(*) from patient_symptom ps2 
                                where ps2.patientId = ps.patientId)
and count(ms.symptomId) = (select count(*) from medication_symptoms ms2 
                                where ms2.MedId = ms.MedId)

更新

如果您使用full join，您可以使用条件聚合来确保在全连接的两侧都没有空值以确保有一个1:1的匹配。

select t1.MedId
from (
  select * from 
  patient_symptom ps
  cross join medicine m
  where patientId = :myPatientId
) t1
full join medication_symptoms ms on t1.SymptomId = ms.SymptomId
      and t1.MediId = ms.MediId
group by t1.MedId
having count(case when t1.SymptomId is null or ms.SymptomId is null then 1 end) = 0

- FuzzyTree

---

我真不知道你是怎么做到这么快的！#尊敬 - Raj Rao

第一个查询仍然导致我得到Med A。但第二个查询正好做我想要的事情。这是什么类型的查询？（以便我可以更多地了解它） - Raj Rao

@RajRao 这是一个带有子查询的聚合查询，我不认为它有一个特定的名称。我会查一下为什么第一个查询不起作用，看起来应该可以。 - FuzzyTree

当我将第一个查询更改为以下内容时，我成功地使其工作：select * from medicine m where not exists ( select 1 from patient_symptom ps full join medication_symptom ms on ps.SymptomId = ms.SymptomId and ps.PatientId = :myPatientIdHere where ms.MedicineId = m.Id and (ms.SymptomId is null or ps.symptomId is null)
) - Raj Rao

似乎问题与患者ID子句的放置位置有关。如果将其放在ON子句中，它可以正常工作。但如果放在WHERE子句中，则无法正常工作。 使用FULL JOIN连接medication_symptom ms和ps表，其中ps.SymptomId = ms.SymptomId并且ps.PatientId = :myPatientIdHere。 - Raj Rao

Answer 2

FuzzyTree发布了多个答案。这是他的第一个查询，我为了使其正常工作所做的所有更改。他的第二个查询也有效。

SELECT *
FROM medicine m
WHERE NOT EXISTS (
        SELECT 1
        FROM (
            SELECT ms.symptomId
            FROM Medication_Symptoms ms
            WHERE ms.medId = m.medid
            ) ms1
        FULL JOIN (
            SELECT ps.SymptomId
            FROM Patient_Symptom ps
            WHERE ps.PatientId = 7
            ) ps1 ON ps1.SymptomId = ms1.SymptomId
        WHERE (
                ps1.SymptomId IS NULL
                OR ms1.symptomId IS NULL
                )
        )

我们发现以下查询比上面的查询更快（这是由一位同事发现的，通过观察墙钟时间和查询计划，这个查询更快）。

select m.Name
from Medicine m
where m.Id in (
    select ms.MedicineId
    from       Medication_Symptom ms
    inner join (select SymptomId
        from Patient_Symptom
        where PatientId = 7)      ps on ps.SymptomId = ms.SymptomId
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = 7)
    intersect
    select ms.MedicineId
    from Medication_Symptom ms
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = 7)
    )

最终，这个查询将返回所有患者的数据：

select po.Name, m.Name
from Medicine m, patient po
where m.Id in (
    select ms.MedicineId
    from       Medication_Symptom ms
    inner join (select SymptomId
        from Patient_Symptom
        where PatientId = po.Id)      ps on ps.SymptomId = ms.SymptomId
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = po.Id)
    intersect
    select ms.MedicineId
    from Medication_Symptom ms
    group by ms.MedicineId
    having count(*) = (select count(SymptomId)
        from Patient_Symptom
        where PatientId = po.Id)
    )

- Raj Rao