mysql - 如何有效地查询两列上的多对多关系

Question

我创建了这三个表，代表医生和医生专业之间的多对多关系。

一个医生可以有很多专长，很多医生可以有一个专长。

CREATE TABLE doctors (
  doctor_id int(11) NOT NULL AUTO_INCREMENT,
  firstname varchar(30) NOT NULL,
  lastname varchar(30) NOT NULL,
  area varchar(50) NOT NULL,
  PRIMARY KEY (doctor_id)
);

CREATE TABLE has_specialty (
  doctor_id int(11) NOT NULL,
  specialty_id int(11) NOT NULL,
  PRIMARY KEY (doctor_id,specialty_id),
  FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id),
  FOREIGN KEY (specialty_id) REFERENCES specialties (specialty_id)
);

CREATE TABLE IF specialties (
  specialty_id int(11) NOT NULL AUTO_INCREMENT,
  specialty varchar(254) NOT NULL UNIQUE,
  PRIMARY KEY (specialty_id)
);

• 我想要的是找到所有具有专业“H”并且在区域“B”中的医生。

例如，假设我们有这个数据库:

DOCTORS
+-----------+-----------+----------+----------+
| doctor_id | firstname | lastname | area     |
+-----------+-----------+----------+----------+
|         1 | Virginia  | Clark    | A        |
|         2 | Jane      | Brown    | B        |
|         3 | Adam      | Harris   | D        |
|         4 | Anthony   | Rogers   | D        |
|         5 | Paula     | Lopez    | B        |
|         6 | Jerry     | Howard   | A        |
|         7 | Willie    | Nelson   | C        |
|         8 | Juan      | Perry    | A        |
|         9 | Victor    | Allen    | B        |
+-----------+-----------+----------+----------+

SPECIALTIES
+--------------+-----------+
| specialty_id | specialty |
+--------------+-----------+
| 1            | A         |
| 2            | B         |
| 3            | C         |
| 4            | D         |
| 5            | E         |
| 6            | F         |
| 7            | G         |
| 8            | H         |
| 9            | I         |
+--------------+-----------+

HAS_SPECIALTY
+-----------+--------------+
| doctor_id | specialty_id |
+-----------+--------------+
| 1         | 4            |
| 1         | 6            |
| 1         | 8            |
| 2         | 3            |
| 2         | 8            |
| 3         | 1            |
| 3         | 4            |
| 3         | 5            |
| 4         | 4            |
| 5         | 8            |
| 5         | 9            |
| 6         | 2            |
| 6         | 7            |
| 7         | 9            |
| 8         | 4            |
| 9         | 2            |
| 9         | 3            |
+-----------+--------------+

结果应该是:

+-----------+-----------+----------+----------+
| doctor_id | firstname | lastname | area     |
+-----------+-----------+----------+----------+
|         2 | Jane      | Brown    | B        |
|         5 | Paula     | Lopez    | B        |
+-----------+-----------+----------+----------+

Answer 1

您可以使用简单的 INNER JOIN 来做到这一点:

SELECT d.doctor_id, d.firstname,d.lastname,d.area     
FROM doctors d
INNER JOIN has_specialty hs ON d.doctor_id = hs.doctor_id
INNER JOIN specialties s ON hs.specialty_id = s.specialty_id
WHERE s.specialty = 'H' AND d.area = 'B';

sqlfiddle demo