mysql里面排名出现问题_Mysql排名问题

简述

最近在刷题和工作中总会遇到前n高，第n高的问题，汇总一下以便日后查看。

排名3种场景(以薪水为例)：

连续排名，同薪不同名。3000、2000、2000、1000的排名为1-2-3-4

不连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-4

连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-3

下面的两个例子都以连续排名，同薪同名的情况举例

第N高薪水(连续排名，同薪同名)

如表中所示，如果存在第N高的薪水则返回Salary，如果不存在那么查询应该返回NULL。

单表查询

解题思路

全局排名，不分组，所以我们可以用ORDER BY排序加LIMIT N,M限制(M表示在限制条数之后的offset记录，LIMIT M OFFSET N)，排名第N高意思是LIMIT N-1,1，但是LIMIT后面只接受正整数或者单一变量，不能用表达式，所以在函数中需要先SET N = N - 1

同薪同名且连续排名，意味着需要去重，我们可以用GROUP BY 按薪水分组后再ORDER BY或者DISTINCT去重。

MySQL中的LIMIT用法详解

基本语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT子句用于select中，对输出结果集的行数进行约束，LIMIT接受一个或两个数字参数。参数必须是一个整数常量。offset表示偏移量(指向数据记录的游标)，rows表示查询限定返回的最大记录数。当offset参数省略时，默认为0，即LIMIT 3 等同于LIMIT 0,3。

SELECT * FROM table LIMIT 3, 4;返回第4-7行

SELECT * FROM table LIMIT 3;返回前3行

代码片段

CREATE FUNCTION getNHighestSalary(N INT) RETURNS INT

BEGIN

SET N := N-1;

IF (N < 0) THEN

RETURN NULL;

ELSE

RETURN (

SELECT DISTINCT salary FROM employee

-- GROUP BY salary

ORDER BY salary DESC

LIMIT N, 1

);

END IF;

END

子查询方式1

解题思路

先查出前n高的薪水，再从中查询最低的薪水(即第n高的薪水)，并用COUNT(1)累加用来判断是否有第n高的薪水 。考虑会有相等的薪水所以第一重查询用DISTINCT去重。

代码片段

CREATE FUNCTION getNHighestSalary(N INT) RETURNS INT

BEGIN

RETURN(

SELECT IF(count < N, NULL, min) AS Salary

FROM

(

SELECT MIN(Salary) AS min, COUNT(1) AS count

FROM

(

SELECT DISTINCT Salary

FROM Employee ORDER BY Salary DESC LIMIT N

) a

) b

);

END

子查询方式2

解题思路

排名第N高意味着表中存在N-1个比其更高的薪水(去重前提下)。

联表查询出比当前薪水高的有几个，如果这个数量等于N-1，那么返回该薪水。

代码片段

CREATE FUNCTION getNHighestSalary(N INT) RETURNS INT

BEGIN

RETURN(

SELECT DISTINCT(e.salary)

FROM Employee e

WHERE (

SELECT

COUNT(DISTINCT salary)

FROM Employee e1 WHERE e1.salary > e.salary

) = N - 1

);

END

自连接

解题思路

自连接条件为表1的Salary小于表2的Salary，以表1的Salary分组，统计表2的Salary的去重个数

考虑到第一名的表2的Salary为空，所以采用LEFT JOIN ，当去重个数等于N-1时就是要输出的排名(也可以用JOIN，连接条件为<=，COUNT(DISTINCT e2.Salary) = N)

代码片段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

SELECT

e1.salary

FROM

employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary

GROUP BY

e1.salary

HAVING

count(DISTINCT e2.salary) = N - 1

);

END

笛卡尔积

解题思路

跟子查询方式2相似，不再赘述。

代码片段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

SELECT

e1.salary

FROM

employee e1, employee e2

WHERE

e1.salary <= e2.salary

GROUP BY

e1.salary

HAVING

count(DISTINCT e2.salary) = N

);

END

自定义变量

解题思路

自定义两个变量，@s存储工资，@r存储排名，先按工资排序，查询时更新变量值，当工资相等时排名不变，不相等则排名加一

代码片段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

SELECT

DISTINCT salary

FROM

(SELECT

salary, @r:=IF(@s=salary, @r, @r+1) AS rnk, @s:= salary

FROM

employee, (SELECT @r:=0, @s:=NULL)init

ORDER BY

salary DESC) tmp

WHERE rnk = N

);

END

开窗函数

解题思路

mysql8.0以上版本可以用开窗函数，效率是最好的，常用的三种排名函数如下：

ROW_NUMBER():连续排名，同薪不同名，3000、2000、2000、1000的排名为1-2-3-4

RANK(): 不连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-4

DESENSE_RANK():连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-3

这三个函数要和OVER()一起使用，OVER()中的参数通常是PARTITION BY 和 ORDER BY 。例题情况是第三种，所以采用DENSE_RANK()。

代码示例

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

SELECT

DISTINCT salary

FROM

(SELECT

salary, dense_rank() over(ORDER BY salary DESC) AS rnk

FROM

employee) tmp

WHERE rnk = N

);

END

部门前n高薪水(连续排名，同薪同名)

如表中所示，如果存在部门前N高的薪水则返回DepartmentId + Salary，如果不存在那么查询应该返回NULL。因为只考虑部门和薪水，所以还是连续排名，同薪同名。

子查询方式

解题思路(和第N高薪水的子查询方式2类似)

工资前N高意味着：有不超过N-1个人的工资比查询结果的工资高。例如求前三高的工资，即有不超过2个人(查询子条件为<=2或<3)的工资比查询结果的工资高(有0个人比第一高工资高；有1个人比第二高工资高；有2个人比第三高工资高)

代码示例

SELECT

d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary

FROM Employee e1

RIGHT JOIN Department d ON e1.DepartmentId = d.Id

WHERE

3 > (SELECT COUNT(DISTINCT e2.Salary)

FROM Employee e2

WHERE e2.Salary > e1.Salary

AND e1.DepartmentId = e2.DepartmentId

)

GROUP BY e1.Salary

ORDER BY d.`Name`, e1.Salary DESC

;

连接查询

解题思路

能用子查询解决的问题一般都能用连接来解决

代码示例

SELECT

d.name as department, e1.name as employee, e1.salary as salary

FROM

Department d LEFT JOIN Employee e1 on d.id = e1.departmentid

LEFT JOIN Employee e2 on e1.departmentid = e2.departmentid and e1.salary<=e2.salary

GROUP BY

d.name, e1.Salary

HAVING

count(distinct e2.salary)<4

ORDER BY

d.name, e1.salary DESC

自定义变量

解题思路

自定义三个变量，@s存储工资，@r存储排名，@d存储部门ID，先按部门和工资排序，查询时更新变量值。

(1)当前部门ID与@d相同(@d=DepartmentId)，则代表是在同一部门中进行的排名，当工资相等(@s=Salary)时排名不变(@r:=@r)，不相等则排名加一(@r:=@r+1)；

(2)当前部门ID与@d不相同(@d!=DepartmentId)，则说明@d需重新赋值(@d=DepartmentId)，排名也要重新开始，即@r:=1。

代码示例

SELECT

d. NAME department,

t. NAME employee,

salary

FROM

(

SELECT

*, @r :=IF (DepartmentId = @d, IF (Salary = @s, @r, @r + 1), 1) AS rnk,

@d := DepartmentId,

@s := Salary

FROM employee, (SELECT @s := NULL,@d := NULL, @r := 0 ) init

ORDER BY DepartmentId, Salary DESC

) t

RIGHT JOIN department d ON t.DepartmentId = d.Id

WHERE t.rnk <= N OR t.rnk IS NULL

GROUP BY d.`Name`, salary

ORDER BY DepartmentId, Salary DESC

开窗函数

解题思路

又到了快乐的开窗函数，因为是同薪同名，连续排名，所以还是用DENSE_RANK()，因为求的是部门前N高薪水，所以按部门分组再按薪水排序，那么开窗函数的使用就是：DENSE_RANK() OVER(PARTITION BY departmentid ORDER BY salary DESC) 。

代码示例

SELECT

d.`Name`, tmp.`Name`, tmp.Salary

FROM(

SELECT

e1.DepartmentId, e1.`Name`, e1.Salary,

DENSE_RANK() OVER(PARTITION BY e1.DepartmentId ORDER BY e1.Salary DESC) rnk

FROM employee e1 ) tmp

RIGHT JOIN department d

ON d.Id = tmp.DepartmentId

WHERE rnk <= N OR t.rnk IS NULL

GROUP BY d.name, tmp.Salary

;