任务:想象外科医生治疗过的病人。表:患者病人身份证名称姓表:医生医生身份证名称姓专业(“外科医生”是一种可能性)表:访问id\访问id\患者\U fk(外键)id\u doctor\u fk(外键)与 id_doctor_fk 指向 id_doctor ,我得看看那个医生是不是外科医生,对吧?这可能吗?我放弃了,所以我要求。
id_doctor_fk
id_doctor
j2datikz1#
如果你想要看过外科医生的病人,你可以使用 exists :
exists
select p.* from patients p where exists (select 1 from visits v join doctors d on d.id_doctor = v.id_doctor_pk where v.id_patient_fk = p.id_patient and d.specialty = 'surgeon' );
monwx1rj2#
我为你的问题举了个例子。我希望这对你有帮助:
CREATE TABLE #patients2 ( id_patient int, [name] VARCHAR(100), last_name VARCHAR(100) ); CREATE TABLE #doctors2 ( id_doctor int, [name] VARCHAR(100), last_name VARCHAR(100), speciality VARCHAR(100), ); CREATE TABLE #dvisits2 ( id_visit int, id_patient_fk int, id_doctor_fk int ); --drop table #table1 INSERT INTO #patients2 (id_patient, [name], last_name) VALUES (1,'patient1','last_name1'), (2,'patient2','last_name2'), (3,'patient3','last_name3'), (4,'patient4','last_name4'), (5,'patient5','last_name5'), (6,'patient6','last_name6'), (7,'patient7','last_name7'), (8,'patient8','last_name8'); INSERT INTO #doctors2 (id_doctor, [name], last_name, speciality ) VALUES (1,'doctor1','last_name1','surgeon'), (2,'doctor2','last_name2','not surgeon'), (3,'doctor3','last_name3','surgeon'), (4,'doctor4','last_name4','not surgeon'), (5,'doctor5','last_name5','surgeon'), (6,'doctor6','last_name6','surgeon'), (7,'doctor7','last_name7','not surgeon'), (8,'doctor8','last_name8','surgeon'); INSERT INTO #dvisits2 (id_visit, id_doctor_fk,id_patient_fk) VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8); select * from #patients2 p join #dvisits2 dv on p.id_patient = dv.id_patient_fk join #doctors2 doc on dv.id_doctor_fk = doc.id_doctor where doc.speciality = 'surgeon'
结果=
id_patient [name] last_name id_visit id_patient_fk id_doctor_fk speciality id_doctor [name] last_name 1 patient1 last_name1 1 1 1 surgeon 1 doctor1 last_name1 1 patient1 last_name1 1 1 1 surgeon 1 doctor1 last_name1 3 patient3 last_name3 3 3 3 surgeon 3 doctor3 last_name3 3 patient3 last_name3 3 3 3 surgeon 3 doctor3 last_name3 5 patient5 last_name5 5 5 5 surgeon 5 doctor5 last_name5 6 patient6 last_name6 6 6 6 surgeon 6 doctor6 last_name6 8 patient8 last_name8 8 8 8 surgeon 8 doctor8 last_name8
2条答案
按热度按时间j2datikz1#
如果你想要看过外科医生的病人,你可以使用
exists
:monwx1rj2#
我为你的问题举了个例子。我希望这对你有帮助:
结果=