mysql - 如何从 GROUP BY 中获取 COUNT(*) 的最大值？

Question

我有这个问题

SELECT Pname, COUNT(*) AS Num
FROM employee
JOIN project
  ON Dno = Dnum
GROUP BY Pname

它提供了这些结果:

Pname                   Num
Computerization         3
DatabaseSystems         8
InkjetPrinters          10
LaserPrinters           10
Middleware              8
Newbenefits             3
OperatingSystems        8
ProductX                4
ProductY                4
ProductZ                4
Reorganization          1

如何查询 Pname 和 Num 以返回计数最高的元素名称？

结果应该是这样的:

InkjetPrinters  10
LaserPrinters   10

Answer 1

您可以使用从子查询中指定的 HAVING 子句。

在 MySQL 中

 SELECT Pname, COUNT(*) AS Num
 FROM employee
 JOIN project
   ON Dno = Dnum
 GROUP BY Pname
 HAVING COUNT(*) = (
  SELECT COUNT(*)
  FROM employee
  JOIN project
   ON Dno = Dnum
  GROUP BY Pname
  ORDER BY COUNT(*) DESC
  LIMIT 1
 )