Mysql连接两个表

Question

现在我有这两张表

doctor_id 及其预约计数

以及医生姓名和他/她的科室名称

我需要计算每个部门的约会，知道如何连接这两个表吗？

Answer 1

只需加入所有表格并更改分组即可。

SELECT d.name AS department, COUNT(r.d_id) AS appointment_count
FROM departments AS d
JOIN userdelprel AS ud ON d.dept_id = ud.dept_id
JOIN users AS u ON u.u_id = ud.u_id
LEFT JOIN roomreservations AS r ON r.d_id = u.u_id
WHERE u.role_id = 2
GROUP BY d.dept_id