如何有效地查询两列上的多对多关系 [英] How to efficiently query many-to-many relationship on two columns

问题描述

我创建了这三个表，它们代表了医生与医生专长之间的多对多关系.

I have created these three tables that represent a many-to-many relationship between a doctor and a doctor's specialties.

一个医生可以有很多专长，许多医生可以有一个特定专长.

A doctor can have many specialties and many doctors can have a certain specialty.

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'地区的医生.

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

So for example lets say we have this database:

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        |
+-----------+-----------+----------+----------+

推荐答案

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

You can do that with simple INNER JOIN's:

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演示

这篇关于如何有效地查询两列上的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！