mysql - 如何使用 max 函数缩小查询范围？

Question

我正在使用 PHP MyAdmin。我希望此查询的结果是 Willard Wildcats 和 7，但我不知道如何通过使用 max 函数而不是 order by 和 limit 函数来实现。我需要使用 max 函数

Select Team_Name, Count(*)
From Number_3
Group By Team_Name

这个查询的结果应该是

Willard Wildcats and 7

Answer 1

使用 LIMIT 子句:

SELECT Team_Name, Count(*) AS CNT 
FROM Number_3
GROUP BY Team_Name
ORDER BY CNT DESC
LIMIT 1;

如果您受 LIMIT 子句限制，则使用子查询:

SELECT Team_Name, Count(*) AS CNT 
FROM Number_3
GROUP BY Team_Name
HAVING Count(*) = (SELECT MAX(CNT)
                   FROM (SELECT Team_Name, Count(*) AS CNT 
                         FROM Number_3
                         GROUP BY Team_Name
                        ) t
                  );

如果您使用的是最新版本的 MySQL，那么使用排名功能会更容易:

SELECT t.*
FROM (SELECT Team_Name, Count(*) AS CNT,
             DENSE_RANK() OVER (ORDER BY Count(*) DESC) AS Seq 
      FROM Number_3
      GROUP BY Team_Name
     ) t
WHERE Seq = 1;