mysql - SQL从计数大于的内部联接中选择

医生

doctorid (PK)
doctorname

Patient

patientid (PK)
patientname
doctorid

I have the following query to get the doctors details with the number of patients that he/she consults. assume a patient has only one doctor to consult.

select d.doctorid,d.doctorname,count(p.patientid)
from doctor d
inner join patient p
on d.doctorid = p.doctorid
group by p.doctorid

现在，我需要获取相同的信息，但仅适用于拥有1名以上患者的医生。请建议我一个查询。

最佳答案

使用 HAVING clause

SELECT d.doctorid,
       d.doctorname,
       COUNT(p.patientid) AS patients
  FROM doctor d
       INNER JOIN patient p
                  ON d.doctorid = p.doctorid
GROUP BY
       d.doctorid,
       d.doctorname
HAVING patients > 1

我使用别名(patients)而不是COUNT(p.patientid)，因为HAVING子句允许这样做。但您也可以坚持使用COUNT(p.patientid)另外，我建议您在GROUP BY子句中使用所有未聚合的列。
而且，如果您检索doctorname，则可能不必检索doctorid。