SQL - 显示计数最大值的条目? [英] SQL - Displaying entries that are the max of a count?

问题描述

CREATE TABLE doctor( patient CHAR(13), docname CHAR(30) );

假设我有一个这样的表格，那么我将如何显示拥有最多患者的医生的姓名?就像如果最多三个，两个医生有三个病人，那么我会显示他们两个的名字.

Say I had a table like this, then how would I display the names of the doctors that have the most patients? Like if the most was three and two doctors had three patients then I would display both of their names.

这将获得最多的患者:

SELECT MAX(count) 
FROM (SELECT COUNT(docname) FROM doctor GROUP BY docname) a;

这是所有医生和他们有多少病人:

This is all the doctors and how many patients they have:

SELECT docname, COUNT(docname) FROM doctor GROUP BY name;

现在我不知道如何将它们组合起来以仅列出拥有最多患者的医生的姓名.

Now I can't figure out how to combine them to list only the names of doctors who have the max patients.

谢谢.

推荐答案

应该这样做.

SELECT docname, COUNT(*) FROM doctor GROUP BY name HAVING COUNT(*) = 
    (SELECT MAX(c) FROM
        (SELECT COUNT(patient) AS c
         FROM doctor
         GROUP BY docname))

另一方面，如果您只需要第一个条目，则

On the other hand if you require only the first entry, then

SELECT docname, COUNT(docname) FROM doctor 
GROUP BY name 
ORDER BY COUNT(docname) DESC LIMIT 1;

这篇关于SQL - 显示计数最大值的条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！