逐段解析HackerRank Top Earners问题SQL查询语句执行逻辑

题目要求 #

定义员工总收入为月薪资salary乘以工作月数months，最高总收入为Employee表中所有员工的总收入最大值。请编写查询语句，查找所有员工的最高总收入，以及拿到最高总收入的员工总人数，最终输出两个以空格分隔的整数。

Employee表结构 #

参考解法（MS SQL Server环境可运行） #

SELECT
  MAX(months * salary),
  COUNT(salary*months)
FROM employee
WHERE salary * months IN (
    SELECT MAX(salary * months)
    FROM employee
);

逐段执行逻辑拆解 #

先明确一个核心规则：SQL的实际执行顺序和写代码的从上到下顺序不一致，永远是先算内层子查询、先做WHERE行过滤，最后才执行外层SELECT的聚合计算，这段代码的执行一共分3步：

• 第一步：执行最内层的子查询

  SELECT MAX(salary * months) FROM employee
  

  这步没有额外逻辑，就是遍历全表所有员工，先算出每个人的总收入=月薪*工作月数，再从所有收入里挑出最高的那个值，相当于先把「最高收入线」算出来，这个子查询永远只返回单个数字，比如全表最高总收入是150000，这里就只返回150000。
• 第二步：执行外层WHERE条件做行过滤

  WHERE salary * months IN (第一步子查询返回的最高收入值)
  

  这步就是逐行校验全表的每一条员工记录，只留下总收入刚好等于最高收入值的行，把所有没拿到顶薪的员工数据全部过滤掉，执行完这步之后，临时结果集里剩下的全是收入最高的那批员工的数据。
• 第三步：执行外层SELECT的两个聚合计算
  这时候临时结果集里全是顶薪员工，两个聚合函数的计算逻辑非常直接：
  • MAX(months * salary)：因为剩下的所有人收入都是同一个最高值，不管取最大、最小还是平均，结果都是那个最高收入数，这里用MAX只是为了符合SQL聚合语法要求，不会出现计算错误
  • COUNT(salary*months)：统计当前临时结果集里一共有多少条有效记录，算出来的就是拿最高收入的员工总人数
    两个计算结果按顺序输出，就是题目要求的两个空格分隔的整数。

补充说明 #

很多人刚看这段代码会疑惑：既然已经过滤出所有顶薪员工了，直接输出最高收入不行吗？当然不行，因为最高收入是动态计算出来的，没法提前写死，而且SELECT里如果用了COUNT这类聚合函数，同层级的非聚合字段是不能直接输出的，用MAX取收入值是最简便的兼容写法。

内容的提问来源于stack exchange，提问作者PaxUltra