协助SQL查询

Question

假设我们有两个表：

1. Patients (ID，名称)
2. Patients_Treatments (PatientID，treatment_code)

我希望查询能够检索所有至少接受了id='999999999'患者所有治疗的病人。

我尝试过很多种组合，但都没有效果，我所得到的只是那些至少接受了“9999999999”疗法中的一种的病人。

Answer 1

一种方法在having子句中使用自连接和比较：

select pt2.patientId
from patient_treatments pt join
     patient_treatments pt2
     on pt.treatment_code = pt2.treatment_code and pt.patientid <> pt2.patientid
where pt.id = '999999999'
group by pt2.patientId
having count(pt2.treatment_code) = (select count(*) from patient_treatments pt where pt.id = '999999999');

注意:此版本假定Patient_Treatments中不存在重复项。

如果数据中有重复项，则可以使用count(distinct)

having count(distinct pt2.treatment_code) = (select count(distinct pt.treatment_code) from patient_treatments pt where pt.id = '999999999');

Answer 2

我的想法是

(1)选择属于病人"999999999“的治疗代码

(2)只选择其治疗代码符合病人"999999999“的治疗代码之一的治疗记录。

(3)用patient_id分组

(4)仅选择与患者"999999999“相同数目的不同治疗代码的患者ID。

select pt.Patient_ID,count(distinct pt.treatment_code)
from
Patient_treatments pt
inner join
(select distinct treatment_code
 from 
 Patient_treatments pt
 where pt.Patient_ID="999999999"
 )t1
 on t1.treatment_code=pt.treatment_code
 where pt.Patient_ID<>"999999999"
 group by pt.Patient_ID
 having count(distinct pt.treatment_code)=
 (select count(distinct treatment_code) 
  from 
  Patient_treatments pt
  where pt.Patient_ID="999999999"
  );

使用以下模式，只选择患者1：

  Create table Patient_Treatments
  (
    Patient_ID varchar(10),
    Treatment_code varchar(10)
    );


   Insert into Patient_Treatments
    values
    ("1","abc"),
    ("1","def"),
    ("1","def"),
    ("1","ghi"),
    ("2","abc"),
    ("2","def"),
    ("2","def"),
    ("3","ghi"),
    ("999999999","abc"),
    ("999999999","def"),
    ("999999999","ghi"),
    ("999999999","ghi")

http://sqlfiddle.com/#!9/03a84b