sql笔记——mysql排名问题-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42186404/article/details/100617034

这篇博客总结了在MySQL中处理排名问题的四种方法：self join、temp table、user variable和sub-selection。针对LeetCode上的SQL题目，这些技巧在没有内置rank函数的情况下尤其有用。作者参考了简书和优快云上的相关文章进行讲解。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

leetcode 的 sql 题目有一些涉及到排名问题，mysql并没有内置rank函数。

主要参考blog，并总结要点

https://www.jianshu.com/p/bb1b72a1623e

https://blog.youkuaiyun.com/xxzhangx/article/details/78170019

1. self join

select a.XX
from table a, table b
where a.XX = b.xx

2. temp table

select count(*) FROM (SELECT distinct zz FROM table) tmp WHERE s >= Score) as YYY
FROM table

主要特征在于 select something from another table originated from the former selection and the result of the former selection is shown as table tmp

3. user variable

select
  Score,
  @rank := @rank + (@prev != (@prev := Score)) as Rank
from
  Scores,
  (select @rank := 0, @prev := -1) r
order by Score desc

这个方法和下面这种方法

SELECT score, 
CASE 
WHEN @prevRank = age THEN @curRank 
WHEN @prevRank := age THEN @curRank := @curRank + 1
END AS rank
FROM table t, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY score

的区别在于，用比较运算返回的布尔值代替case分类返回增量，有点巧妙的trick

4.sub-selection

SELECT
  Score,
  (SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
ORDER BY Score desc

在select里嵌套select的查询结果，不是常见的sub-selection，那种写在where或者什么与语句里的 where XX=（select XXXX）

------END