mysql - 如何使用SQL查询表以给出ID的实例数？

我有两张桌子Doctor and Appointment。我需要列出医生ID的列表，其中包含为每个具有一个或多个约会的医生所进行的约会数量。

到目前为止，我有，但是我不知道如何使它完成我想做的事情：

SELECT
    doctor.doctor_id,
    appointment.doctor_id
FROM doctor,
    appointment
WHERE doctor.doctor_id =     appointment.doctor_id;

SELECT
    COUNT(DISTINCT doctor_id) AS NumberOfAppointments
FROM appointment
where doctor_id="50";

任何帮助，将不胜感激，谢谢。

最佳答案

JOIN两张桌子和使用GROUP BY

SELECT doctor.doctor_id,
       appointment.doctor_id,
       COUNT(doctor_id) AS NumberOfAppointments

FROM doctor
LEFT JOIN appointment ON doctor.doctor_id = appointment.doctor_id GROUP BY doctor_id;

关于mysql - 如何使用SQL查询表以给出ID的实例数？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22768988/

APPOINTMENT

mysql - 如何使用SQL查询表以给出ID的实例数？