我有这个查询
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
您可以使用从子查询中指定的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
)