mysql rank 排序

来源：互联网发布：微信红包牛牛源码教程编辑：程序博客网时间：2024/05/19 14:20

原题链接：https://leetcode.com/problems/rank-scores/

scores表+----+-------+| Id | Score |+----+-------+| 1  | 3.50  || 2  | 3.65  || 3  | 4.00  || 4  | 3.85  || 5  | 4.00  || 6  | 3.65  |+----+-------+

按照分数排名+-------+------+| Score | Rank |+-------+------+| 4.00  | 1    || 4.00  | 1    || 3.85  | 2    || 3.65  | 3    || 3.65  | 3    || 3.50  | 4    |+-------+------+

1：思路

首先定义 初始排名的值的0, 然后 查询的每一条数据 @rank这个变量就会+1。 这就是一个比较简单的排名， 但是你会发现 score数值一样的话，排名应该是一样的，因此这个并不正确。mysql> set @rank = 0;mysql> select score, (@rank := @rank + 1) as rank from scores order by score desc;+-------+------+| score | rank |+-------+------+|  4.00 |    1 ||  4.00 |    2 ||  3.85 |    3 ||  3.65 |    4 ||  3.65 |    5 ||  3.50 |    6 |+-------+------+6 rows in set (0.00 sec)

2：比较前一个数值

mysql> set @rank = 0;Query OK, 0 rows affected (0.00 sec)mysql> set @prev = null;Query OK, 0 rows affected (0.00 sec)mysql> select score, if (@prev = score, @rank ,@rank := @rank + 1 ) as Rank , (@prev := score) as tmp from scores order by score desc;+-------+------+------+| score | Rank | tmp  |+-------+------+------+|  4.00 |    1 | 4.00 ||  4.00 |    1 | 4.00 ||  3.85 |    2 | 3.85 ||  3.65 |    3 | 3.65 ||  3.65 |    3 | 3.65 ||  3.50 |    4 | 3.50 |+-------+------+------+6 rows in set (0.00 sec)

0 0