本文共 993 字，大约阅读时间需要 3 分钟。

思路

代码

SELECT d.Name AS Department, e.Name AS Employee, e.SalaryFROM Department AS dLEFT JOIN (    SELECT e.*, MAX(Salary) AS MaxSalary    FROM Employee AS e    GROUP BY e.DepartmentId    HAVING MaxSalary = e.Salary) AS e_selected ON d.Id = e.DepartmentId

解释

• SELECT d.Name AS Department, e.Name AS Employee, e.Salary：从部门表 d 中获取部门名称，并从 e_selected 中获取员工名称及其最终确认到的最高工资。

• LEFT JOIN：确保即使某部门没有员工，结果中仍然会显示该部门。

• 子查询SELECT e.*, MAX(Salary) AS MaxSalary FROM Employee AS e GROUP BY e.DepartmentId HAVING MaxSalary = e.Salary：

  • GROUP BY e.DepartmentId：按部门 Id 分组。
  • MAX(Salary)：在组内找出最高的工资。
  • HAVING MaxSalary = e.Salary：只保留那些刚好等于组内最高工资的员工信息。如果有多个员工在同一部门的工资相同，这些员工都会被保留。

用这种方法可以既高效又清晰地得到每个部门的工资最高员工信息。无论是单一部门的最高工资员工，还是多个员工的最高工资信息，都可以通过这一查询轻松获取。