mysql - 如何在 MySQL 中仅通过外键检索数据？

Question

任务：可视化接受外科医生治疗的患者。

表：患者

• id_耐心
• 姓名
• 姓

表：医生

• id_doctor
• 姓名
• 姓
• 专业（“外科医生”是其中一种可能性）

表：访问

• id_visit
• id_patient_fk（外键）
• id_doctor_fk（外键）

id_doctor_fk指着，我得id_doctor看看那个医生是不是外科医生，对吗？这可能吗？我放弃了，所以我问。

标签： mysqlsqlforeign-keys

Answer 1

我为您的问题创建了一个示例。我希望这对你有帮助：

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