php - 对特定列进行排名

Question

我有一张表，上面有员工的 id、姓名、薪水。我想根据薪水对员工进行排名。

表:雇员

id   employee_name  employee_salary
------------------------------------
 1   Martin            3500
 2   Su                4000
 3   Alex              3500
 4   Flora             8000     

我想要的结果是

id   employee_name   employee_salary   Rank
--------------------------------------------
 1   Martin            3500             3
 2   Su                4000             2
 3   Alex              3500             3
 4   Flora             8000             1   

我尝试使用 rank() 函数，查询是

select 
    salary, 
    rank() over (order by employee_salary desc) AS Rank 
from employee

这会抛出一个 mysql 错误:

3 errors were found during analysis.

An alias was previously found. (near "Rank" at position 61)
An alias was expected. (near " " at position 60)
Unexpected token. (near "Rank" at position 61

查询有什么问题吗？

Answer 1

如果不使用变量，您可以获得排名

select a.*,
  (select count(*) + 1 
   from employee 
   where employee_salary > a.employee_salary) as rank
from employee a

Demo

Mysql 8 支持window functions