如何使用 sql 从此表中获取总约会持续时间

Question

我有 3 个 mysql 表：

约会

id  slot_id patient_name    doctor_id   deleted_at
1   11      Tasin           23          2019-10-10
2   12      Nawaz           22          null
3   13      Rakib           23          null
4   14      Hossen          23          null
5   15      Aritra          24          null
6   16      Anik            22          null
7   17      Manik           22          null

医生

id  status  doctor_name
22  1       Khaled
23  1       Hasan
24  0       Rumi

插槽

id  date        duration    time
11  2019-10-10  2900        01:01
12  2019-10-11  1200        02:01
13  2019-10-18  1100        03:01
14  2019-09-08  200         11:01
15  2019-08-01  500         01:31
16  2019-10-07  300         02:31
17  2019-10-02  1200        03:31

现在，我想使用 SQL 查询按降序显示医生列表及其总预约持续时间。

不幸的是，我对这个SQL查询一无所知。你能帮我吗？

Answer 1

SELECT DOCTOR_NAME, SUM(DURATION) FROM APPOINTMENTS A
JOIN DOCTORS D ON D.ID = A.DOCTOR_ID
JOIN SLOTS S ON S.ID = A.SLOT_ID
GROUP BY D.ID, DOCTOR_NAME
ORDER BY SUM(DURATION) DESC;

Answer 2

select d.doctor_id, d.doctor_name, sum(apt.duration) as total_duration from 
doctors as d
join appointments as apt on apt.doctor_id = d.doctor_id
join slots as s on s.id = apt.slot_id
group by d.doctor_id, d.doctor_name

上面的查询应该可以正常工作。 可能有一些错别字，因为我不是在SQL管理工作室中编写的。