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

问题描述

（$ CHAR（13），docname CHAR（30））;

假设我有这样的表格，那么我将如何显示具有大多数患者？如果最多的是三个，两个医生有三个病人，那么我会显示他们的两个名字。

这会得到最大患者数：

  SELECT MAX（count）
 FROM（SELECT COUNT（docname）FROM doctor GROUP docname）a; 
  

这是所有的医生和他们有多少患者：

 选择docname，COUNT（docname）FROM doctor GROUP BY名称; 
  

现在我无法弄清楚如何合并它们以仅列出拥有最大的患者。

谢谢。

  SELECT docname，COUNT（*）FROM doctor GROUP BY name HAVING COUNT（*）= 
（SELECT MAX c）FROM 
（SELECT COUNT（patient）as c 
 FROM doctor 
 GROUP BY docname））
  

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

  SELECT docname，COUNT （docname）FROM doctor 
 GROUP BY名称
 ORDER BY COUNT（docname）DESC LIMIT 1; 
  

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.

This would get the max patients:

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.

Thanks.

解决方案

This should do it.

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屋！