我有三张桌子：医生、人事和预约。
医生表：
+-----------+----------+---------+----------------+----------------+
| doctor_id | phone_no | room_no | date_qualified | date_appointed |
+-----------+----------+---------+----------------+----------------+
| 50 | 1234 | 1 | 1963-09-01 | 1991-05-10 |
| 51 | 1235 | 2 | 1973-09-12 | 1991-05-10 |
| 52 | 1236 | 3 | 1990-10-02 | 1993-04-01 |
| 53 | 1237 | 4 | 1965-06-30 | 1994-03-01 |
+-----------+----------+---------+----------------+----------------+
人员表
+-----------+----------+-----------+---------------+------+
| person_id | initials | last_name | date_of_birth | sex |
+-----------+----------+-----------+---------------+------+
| 100 | T | Williams | 1972-01-12 | m |
| 101 | J | Garcia | 1981-03-18 | f |
| 102 | W | Fisher | 1950-10-22 | m |
| 103 | K | Waldon | 1942-06-01 | m |
| 104 | P | Timms | 1928-06-03 | m |
| 105 | A | Dryden | 1944-06-23 | m |
| 106 | F | Fogg | 1955-10-16 | f |
| 150 | T | Saj | 1994-06-17 | m |
| 50 | A | Cameron | 1937-04-04 | m |
| 51 | B | Finlay | 1948-12-01 | m |
| 52 | C | King | 1965-06-06 | f |
| 53 | D | Waldon | 1938-07-08 | f |
+-----------+----------+-----------+---------------+------+
预约表
+-----------+------------+------------+-----------+---------------+
| doctor_id | patient_id | appt_date | appt_time | appt_duration |
+-----------+------------+------------+-----------+---------------+
| 50 | 100 | 1994-08-10 | 10:00:00 | 10 |
| 50 | 100 | 1994-08-16 | 10:50:00 | 10 |
| 50 | 102 | 1994-08-21 | 11:20:00 | 20 |
| 50 | 103 | 1994-08-10 | 10:10:00 | 10 |
| 50 | 104 | 1994-08-10 | 10:20:00 | 20 |
| 52 | 102 | 1994-08-10 | 10:00:00 | 10 |
| 52 | 105 | 1994-08-10 | 10:10:00 | 10 |
| 52 | 150 | 2014-03-10 | 12:00:00 | 15 |
| 53 | 106 | 1994-08-10 | 11:30:00 | 10 |
+-----------+------------+------------+-----------+---------------+
我需要创建一个查询来生成一个
doctor IDs列表以及他们的姓名和他们的约会次数。我已经创建了一个语句来生成一个医生ID列表，其中包含他们的预约次数，但我不确定如何生成一个包含医生ID及其姓名的列表。
我现在的声明是：
select doctor.doctor_id, count(appointment.appt_time) as no_appt
from doctor
left join appointment
on doctor.doctor_id = appointment.doctor_id
group by doctor.doctor_id;
请帮忙。
最佳答案
您需要一个到person表的附加连接。很显然，doctor_id是链接。讨厌。这应该是一个显式列，而不是重复使用id。
select d.doctor_id, p.initials, p.last_name, count(appointment.appt_time) as no_appt
from doctor d left join
appointment a
on d.doctor_id = a.doctor_id left join
person p
on d.doctor_id = p.person_id
group by d.doctor_id, p.initials, p.last_name;
在mysql中，您实际上不需要将这两列添加到
group by，但这样做是一种很好的做法。关于mysql - SQL数据库-查询以产生医生ID及其名称的列表以及他们拥有的约会数量？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22712235/