Mysql 排名和名次问题

SQL分数排名实现方法

最新推荐文章于 2024-07-18 03:57:49 发布

原创最新推荐文章于 2024-07-18 03:57:49 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#rownum #mysql 行号 #mysql rownum #mysql 排名

sql 专栏收录该内容

5 篇文章

订阅专栏

本文探讨了力扣中关于分数排名的SQL问题，详细解析了三种解决方案：窗口函数法（本地环境不支持）、自关联查询法（效率低）及自变量法（效率高）。并针对现实场景中的排名需求进行了算法改进。

今天在力扣看到一道sql问题，链接https://leetcode-cn.com/problems/rank-scores/

讲的是如何根据分数排名，官网显示中等难度。

大概看了3种解决方法。

方法1，参考oracle用窗口函数，本地调试，不支持

方法2，用自关联查询的方式，官方显示是正确答案，本人亲测，在12w行数据里面跑，240秒没有跑完

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

方法3，使用自变量方式，@a 类似在 Oracle 中的rownum，12w行数据4秒多

SELECT Score, Rank FROM
(SELECT Score,
@curRank := IF(@prevRank = Score, @curRank+0,@curRank:=@curRank+1) AS Rank,
@prevRank := Score
FROM Scores, (
SELECT @curRank :=0, @prevRank := NULL
) r
ORDER BY Score DESC) s

引申：

1、其实现实中，比如单位绩效考核排名，学校成绩排名，相同排名后的人肯定要加上排名相同的人数，如两个100分，

后面有人99分，那这个人应该是第三名而不是第二名。所以根据方法3改变写法如下

SELECT Score, Rank, ROWNUM FROM
(SELECT
Score,
@curRank := IF(@prevRank = Score, @curRank+0,@curRank:=@totalRank+1) AS Rank,
@totalRank := @totalRank +1 AS ROWNUM,
@prevRank := sale_limited_price
FROM scores, (
SELECT @curRank :=0, @prevRank := NULL, @totalRank := 0
) r
ORDER BY Score DESC) s

2、本地mysql版本目前不支持方法1的窗口函数和rownum写法，附上rownum写法

select score, rownum from 
(
select score,
@rownum := @rownum+1  as rownum
from scores,(select @rownum := 0) r order by score desc
) s