mysql - 为每位医生制作一份预约 list ；包括医生姓名、患者姓名、日期、时间和持续时间

Question

我有三个表；

• 博士
• 个人
• 预约

表结构为:

医生表:

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

我需要创建一个查询来生成每位医生的预约列表，包括医生姓名、患者姓名以及预约的日期时间和持续时间。结果应按医生姓名和预约日期/时间排序。

我尝试了以下代码:

select person.last_name, person.initials, person.last_name, appointment.appt_date, appointment.appt_time, appointment.appt_duration -> from doctor inner join appointment on doctor.doctor_id = appointment.doctor_id -> inner join person on person.person_id = appointment.patient_id -> order by person.initials, person.last_name, appointment.appt_date, appointment.appt_time;

我得到了以下结果，其中没有医生姓名，而且姓氏也有 2 列，而本应只有一列。

+-----------+----------+-----------+------------+-----------+---------------+ 
| last_name | initials | last_name | appt_date  | appt_time | appt_duration | 
+-----------+----------+-----------+------------+-----------+---------------+ 
| Dryden    | A        | Dryden    | 1994-08-10 | 10:10:00  | 10            | 
| Fogg      | F        | Fogg      | 1994-08-10 | 11:30:00  | 10            | 
| Waldon    | K        | Waldon    | 1994-08-10 | 10:10:00  | 10            | 
| Timms     | P        | Timms     | 1994-08-10 | 10:20:00  | 20            | 
| Saj       | T        | Saj       | 2014-03-10 | 12:00:00  | 15            | 
| Williams  | T        | Williams  | 1994-08-10 | 10:00:00  | 10            | 
| Williams  | T        | Williams  | 1994-08-16 | 10:50:00  | 10            | 
| Fisher    | W        | Fisher    | 1994-08-10 | 10:00:00  | 10            | 
| Fisher    | W        | Fisher    | 1994-08-21 | 11:20:00  | 20            | 
+-----------+----------+-----------+------------+-----------+---------------+

Answer 1

姓氏出现 2 次，因为您选择了 2 次

select person.last_name, <------ here
person.initials, person.last_name, <---- here
appointment.appt_date, 
appointment.appt_time, 
appointment.appt_duration 
from doctor  
inner join appointment on  doctor.doctor_id = appointment.doctor_id 
inner join person on  person.person_id = appointment.patient_id
order by 
person.initials,
person.last_name, 
appointment.appt_date, 
appointment.appt_time;

删除其中一个

医生姓名未显示，因为您没有选择它。你的医生表没有你显示的名字。检查您的数据库是否有名称，然后将其添加到查询中。

根据您的要求检查以下内容是否有帮助!!

select person.last_name, 
person.initials, 
d.last_name as DoctorName, 
appointment.appt_date, 
appointment.appt_time, 
appointment.appt_duration 
from appointment  
inner join doctor on  doctor.doctor_id = appointment.doctor_id 
left join person on  person.person_id = appointment.patient_id
left join 
( 
  select * from person 
)d
on  d.person_id = appointment.doctor_id 
order by 
person.initials,
person.last_name, 
appointment.appt_date, 
appointment.appt_time;

http://sqlfiddle.com/#!2/df2e0a/14