使用SQL的HAVING子句与聚合函数

Question

有人能告诉我为什么这个查询会出错吗？

编写一个查询，找出所有员工中最高总薪酬（earnings）以及拥有最高总薪酬的员工总数（num_employees）。计算方法是：总薪酬 = 工作月数 * 薪资
我们定义一个员工的总薪酬为他们的月薪（salary）乘以工作月数（months）。

SELECT
    (salary * months) as earnings,
    count(*) as num_employees
FROM
    employee
GROUP BY
    salary * months
HAVING
    max(salary * months) = salary * months

devoured elysium · Answer

您的代码中存在逻辑问题。您要知道，《HAVING》子句是用来对分组后的结果数据进行过滤操作的，因此在《HAVING》子句中直接使用MAX函数可能无法得到您预期的结果。

如果您想找到最高的总薪酬及其对应员工人数，可以首先使用子查询找出最高的总薪酬，然后再将它与原始表进行连接，如下所示：

SELECT
  e.salary * e.months as earnings,
  COUNT(*) as num_employees
FROM employee e
JOIN (
  SELECT MAX(salary * months) as max_earnings
  FROM employee
) max_earnings_query
ON e.salary * e.months = max_earnings_query.max_earnings
GROUP BY e.salary * e.months;

Ben James · Answer

按照薪酬降序排列，并仅选择最高薪酬的那一行：

符合ANSI/ISO SQL标准的查询语句如下：

SELECT
    (salary * months) as earnings,
    count(*) as num_employees
FROM
    employee
GROUP BY
    salary * months
ORDER BY
    earnings DESC
FETCH FIRST 1 ROW ONLY

对于FETCH FIRST 1 ROW ONLY这一非标准语法，不同数据库系统中有不同的替代方案，如MySQL中的LIMIT 1和SQL Server中的SELECT TOP 1：