如何使用 SQL 查询表以提供 ID 的实例数? [英] How do I query a table to give the number of instances of an ID using SQL?

问题描述

我有两张表 Doctor 和 Appointment.我需要生成一份医生 ID 列表，其中包含为每位有一次或多次预约的医生进行的预约次数.

I have two tables Doctor and Appointment. I need to produce a list of doctor IDs with the number of appointments made for each doctor with one or more appointments.

到目前为止我有，但我不知道如何让它做我想做的事:

So far I have but I can't figure out how to get it to do what I want :

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";

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

Any help would be appreciated, thanks.

推荐答案

JOIN 两个表并使用 GROUP BY

JOIN TWO TABLES AND USE 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;

这篇关于如何使用 SQL 查询表以提供 ID 的实例数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！