我认为这是最简单的解决方案:
SELECT *
FROM
(SELECT *
FROM Employee
ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
-
SELECT *
:返回所有字段。
-
FROM Employee
:表已搜索。
-
(SELECT *...)
子查询:返回所有人员,按薪资排序。
-
GROUP BY employeesub.Salary
:强制将每个员工的排名靠前的薪资行作为返回结果。
如果你碰巧只需要一排,那就更简单了:
SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1
我还认为最容易分解、理解和修改为其他目的:
-
ORDER BY Employee.Salary DESC
:按薪资排序结果,薪资最高者优先。
-
LIMIT 1
:只返回一个结果。
理解这种方法,解决任何类似的问题都变得很简单:获得最低工资的员工(变更
DESC
到
ASC
),获得收入最高的前十名员工(变动
极限1
到
LIMIT 10
),按其他字段排序(更改
ORDER BY Employee.Salary
到
ORDER BY Employee.Commission
等)。