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

SQL - Displaying entries that are the max of a count?

提问by Stewage

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.

谢谢。

回答by manish_s

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;

回答by squillman

This should do it for you:

这应该为你做：

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)

回答by Mark Byers

Here's another alternative that only has one subquery instead of two:

这是另一种只有一个子查询而不是两个子查询的替代方法：

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

回答by Thomas

Allowing for any feature in any ISO SQL specification since you did not specify a database product or version, and assuming that the table of patients is called "patients" and has a column called "docname", the following might give you what you wanted:

由于您没有指定数据库产品或版本，因此允许任何 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

回答by JonH

Take both queries and join them together to get the max:

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

 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 

回答by Kevin Swann

While using ... HAVING COUNT(*) = ( ...MAX().. ) works:

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

• Within the query, it needs almost the same sub-query twice.
• For most databases, it needs a 2nd level sub-query as MAX( COUNT(*) ) is not supported.

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

While using TOP / LIMIT / RANK etc works:

使用 TOP / LIMIT / RANK 等时：

• It uses SQL extensions for a specific database.

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

Also, using TOP / LIMIT of 1 will only give one row - what if there are two or more doctors with the same maximum number of patients?

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

I would break the problem into steps:

我会将问题分解为以下步骤：

Get target field(s) and associated count

获取目标字段和相关计数

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

Using the above as a 'statement scoped view', join to get the max count row(s)

使用上述作为“语句范围视图”，加入以获得最大计数行

WITH x AS
(
    SELECT docName, COUNT( patient ) AS countX
    FROM doctor
    GROUP BY docName
)
SELECT x.docName, x.countX
FROM x
INNER JOIN
(
    SELECT MAX( countX ) AS maxCountX
    FROM x
) x2
ON x2.maxCountX = x.countX

The WITH clause, which defines a 'statement scoped view', effectively gives named sub-queries that can be re-used within the same query.

WITH 子句定义了一个“语句范围视图”，有效地提供了可以在同一查询中重复使用的命名子查询。

The JOIN matches the row(s) of the maximum count of the patients.

JOIN 匹配患者最大计数的行。

While this solution, using statement scoped views, is longer, it is:

虽然这个使用语句范围视图的解决方案更长，但它是：

• Easier to test
• Self documenting
• Extendable

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

It is easier to test as parts of the query can be run standalone.

由于查询的部分可以独立运行，因此测试更容易。

It is self documenting as the query directly reflects the requirement ie the statement scoped view lists the target field(s) and associated count.

它是自我记录的，因为查询直接反映了需求，即语句范围视图列出了目标字段和相关计数。

It is extendable as if other conditions or fields are required, this can be easily added to the statement scoped view. eg in this case, the table stucture should be changed to include a doctor-id as a primary key field and this should be part of the results.

它是可扩展的，就好像需要其他条件或字段一样，这可以轻松添加到语句范围视图中。例如，在这种情况下，表结构应该更改为包含一个医生 ID 作为主键字段，这应该是结果的一部分。

回答by Boney

Another alternative using CTE:

使用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)