SQL - 显示计数最大值的条目？

Question

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

假设我有一个这样的表，那么我如何显示病人最多的医生的名字呢？就像如果最多是三个并且两个医生有三个病人，那么我会显示他们两个的名字。

这将获得最大患者数：

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

这是所有医生以及他们有多少患者：

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

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

谢谢。

Answer 1

这应该可以做到。

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 name 
ORDER BY COUNT(docname) DESC LIMIT 1;

Answer 2

这应该适合你：

SELECT docname
FROM doctor
GROUP BY docname
HAVING COUNT(patient)=
    (SELECT MAX(patientcount) FROM
        (SELECT docname,COUNT(patient) AS patientcount
         FROM doctor
         GROUP BY docname) t1)

Answer 3

这是另一种选择，它只有一个子查询而不是两个：

SELECT docname
FROM author
GROUP BY name
HAVING COUNT(*) = (
    SELECT COUNT(*) AS c
    FROM author
    GROUP BY name
    ORDER BY c DESC
    LIMIT 1
)

Answer 4

由于您没有指定数据库产品或版本，因此允许任何 ISO SQL 规范中的任何功能，并假设患者表称为“患者”并具有名为“docname”的列，以下内容可能会为您提供您想要的内容：

With PatientCounts As
    (
    Select docname
        , Count(*) As PatientCount
    From patient
    Group By docname
    )
    , RankedCounts As
    (
    Select docname, PatientCount
        , Rank() Over( Order By PatientCount ) As PatientCountRank
    From PatientCounts
    )
Select docname, PatientCount, PatientCountRank
From RankedCounts 
Where PatientCountRank = 1

Answer 5

当使用 ... HAVING COUNT(*) = ( ...MAX().. ) 有效时：

• 在查询中，它需要几乎相同的子查询两次。
• 对于大多数数据库，它需要一个二级子查询 MAX( COUNT(*) )
  不支持。

虽然使用 TOP / LIMIT / RANK 等有效：

• 它使用特定数据库的 SQL 扩展。

另外，使用 TOP / LIMIT 为 1 只会给出一行 - 如果有两个或更多医生具有相同的最大患者数怎么办？

我会将问题分解为几个步骤：

获取目标字段和关联的计数

SELECT docName, COUNT( patient ) AS countX
FROM doctor
GROUP BY docName

使用上面的内容作为 CTE（语句范围视图），使用它来获取最大计数行（ s)

WITH x AS
(
    SELECT docName, COUNT( patient ) AS countX
    FROM doctor
    GROUP BY docName
)
SELECT x.docName, x.countX
FROM x
WHERE x.countX = ( SELECT MAX( countX ) FROM x )

WITH 子句定义了 CTE，有效地提供了可以在同一查询中重复使用的命名子查询。

虽然这个使用 CTE 的解决方案较长，但它：

• 更容易测试
• 自记录
• 可扩展

测试更容易，因为查询的一部分可以独立运行。

它是自记录的，因为查询直接反映了需求
即 CTE 列出目标字段和相关计数。

它是可扩展的，就像需要其他条件或字段一样，可以轻松地将其添加到 CTE。
例如，在这种情况下，应该更改表结构以包含医生 ID 作为主键字段，并且这应该是结果的一部分。

Answer 6

获取两个查询并将它们连接在一起以获得最大值：

 SELECT
      docName,
      m.MaxCount
    FROM
      author
    INNER JOIN
     (
      SELECT 
            MAX(count)  as MaxCount,
            docName
      FROM 
            (SELECT 
                  COUNT(docname) 
             FROM 
                  doctor 
             GROUP BY 
                  docname
            )
      ) m ON m.DocName = author.DocName 

Answer 7

另一种使用 CTE 的替代方案：

with cte_DocPatients
as
(
select docname, count(*) as patientCount
from doctor
group by docname
)
select docname, patientCount from 
cte_DocPatients where
patientCount = (select max(patientCount) from cte_DocPatients)

Answer 8

如果您不需要关心性能，我认为只需排序并选择第一个元素即可。像这样的事情：

SELECT docname, COUNT(docname) as CNT 
FROM doctor 
WHERE docname = docname 
GROUP BY docname
ORDER BY CNT DESC
LIMIT 1

Answer 9

这将为您提供每位医生的姓名以及各自治疗患者的数量

SELECT docname, COUNT(docname) as TreatingPatients FROM doctor
WHERE docname = docname
GROUP BY docname