MAX函数在MYSQL中选择多个值

Question

我正在使用MYSQL获得最大数量的学生，一个助教。通过这个查询，我得到了每个助教的所有助教和学生人数

SELECT TA.Name as Name, COUNT(Lecture.Student) AS studentcount 
FROM TA 
JOIN Lecture 
WHERE TA.TA_PUID = Lecture.TA 
GROUP BY TA.Name
ORDER BY studentcount DESC;

我的输出是

David    2 
Justin   2
Matt     2
Jennifer 1
Hannah   1
Timothy  1
Bob      1

我只想得到助教与学生的最大价值。我怎样才能做到这一点？在这种情况下，我希望我的输出是

David  2 
Justin 2
Matt   2

Answer 1

在MySQL 8之前，您必须这样做：

SELECT TA.Name as Name, COUNT(Lecture.Student) AS studentcount 
FROM TA INNER JOIN Lecture ON TA.TA_PUID = Lecture.TA 
GROUP BY TA.Name
HAVING studentCount = (
   SELECT COUNT(Student) AS studentcount 
   FROM Lecture
   GROUP BY TA
   ORDER BY studentcount DESC
   LIMIT 1
);

但是，如果您使用的是MySQL 8+，那么窗口函数和公共表表达式无疑是首选。

Answer 2

您可以使用rank()

SELECT t.*
FROM (SELECT TA.Name as Name, COUNT(*) AS studentcount,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM TA JOIN
           Lecture l
           ON TA.TA_PUID = l.TA
      GROUP BY TA.Name
    ) t
WHERE seqnum = 1;

注意，JOIN后面应该是一个ON子句，而不是一个WHERE子句。

Answer 3

您没有提到MySQL的版本。在MySQL 8.x中，可以使用CTE (公共表表达式)预计算值。那么，过滤就容易了。

例如：

with
a as (
  SELECT TA.Name as Name, COUNT(Lecture.Student) AS studentcount 
  FROM TA 
  JOIN Lecture 
  WHERE TA.TA_PUID = Lecture.TA 
  GROUP BY TA.Name
)
select * from a where studentcount = (select max(studentcount) from a)