本文介绍了如何有效地查询两列上的多对多关系的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了这三个表,它们代表了医生与医生专长之间的多对多关系.
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';
这篇关于如何有效地查询两列上的多对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!