MySQL排名问题方法总结

原创已于 2022-05-08 19:56:42 修改 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

于 2022-05-05 16:41:55 首次发布

本文详细介绍如何在MySQL中实现连续排名、同薪同名排名以及同薪同名占位排名的查询策略，通过实例演示了case、row_number()、dense_rank()和rank()函数的使用，适合数据库管理和数据处理人员参考。

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号
同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3

不同的应用场景可能需要不同的排名结果，也意味着不同的查询策

(一：首先学习一下用到的相关知识)

mysql中case when then 用法：

--简单case函数
case sex
when '1' then '男'
when '2' then '女’
else '其他' end

--case搜索函数
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end

简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式

@rownum := @rownum + 1 中 := 是赋值的作用，这句话的意思是先执行@rownum + 1，然后把值赋给@rownum；

(SELECT @rownum := 0) r 这句话的意思是设置rownum字段的初始值为0，即编号从1开始。

---用rank起别名时：rank要加引号--”rank“，因为rank本身是个函数，直接写rank会报错

(二：下面开始不同场景排名的查询策略的展示)

第一步：先创建一个简单的表用于本次查询：

create table t_salgray(id int,score int);

第二步：在表中插入数据：

mysql> insert into t_salgray(id,score) values('1',100);
mysql> insert into t_salgray(id,score) values('2',100);
mysql> insert into t_salgray(id,score) values('3',200);
mysql> insert into t_salgray(id,score) values('4',300);
mysql> insert into t_salgray(id,score) values('5',400);
mysql> insert into t_salgray(id,score) values('6',400);
mysql> insert into t_salgray(id,score) values('7',500);

第三步：开始排名

第一种情况：连续排名（1，2，3，4，5，6，7）

法一：

mysql> 
select 
    @paixu := @paixu + 1 as '排名',score 
from 
    t_salgray,(select @paixu := 0)init 
order by 
    score desc;
+------+-------+
| 排名 | score |
+------+-------+
|    1 |   500 |
|    2 |   400 |
|    3 |   400 |
|    4 |   300 |
|    5 |   200 |
|    6 |   100 |
|    7 |   100 |
+------+-------+
7 rows in set, 2 warnings (0.00 sec)

法二：row_number() over

mysql> select score,row_number() OVER(order by score ) as "rank" from t_salgray;
+-------+------+
| score | rank |
+-------+------+
|   100 |    1 |
|   100 |    2 |
|   200 |    3 |
|   300 |    4 |
|   400 |    5 |
|   400 |    6 |
|   500 |    7 |
+-------+------+
7 rows in set (0.00 sec)

第二种情况：数据相同的排名就一样，排名依次排序（1,2,2,3,3,4,5.....）

法一：

mysql> 
select 
    score,case 
    when @rowtotal = score then @paixu 
    when @rowtotal := score then  @paixu := @paixu + 1 
    end as '排名' 
from 
    t_salgray,(select @paixu := 0,@rowtotal := null)r 
order by 
    score desc;
+-------+------+
| score | 排名 |
+-------+------+
|   500 |    1 |
|   400 |    2 |
|   400 |    2 |
|   300 |    3 |
|   200 |    4 |
|   100 |    5 |
|   100 |    5 |
+-------+------+
7 rows in set, 4 warnings (0.00 sec)

法二：dense_rank() over

mysql> select score, dense_rank() over (order by score desc) as 'rank' from t_salgray;
+-------+------+
| score | rank |
+-------+------+
|   500 |    1 |
|   400 |    2 |
|   400 |    2 |
|   300 |    3 |
|   200 |    4 |
|   100 |    5 |
|   100 |    5 |
+-------+------+
7 rows in set (0.01 sec)

第三种情况：数据相同的排名一样，但是相同排名也占位，排名依次排序（1,2,2,4,5,5,7.....）

法一：

mysql> 
SELECT 
    w.score,w.排名 
FROM 
    (
    SELECT 
        s.score,@paixu := @paixu + 1,@incrnum := CASE 
        WHEN @rowtotal = s.score THEN @incrnum 
        WHEN @rowtotal := s.score THEN @paixu END AS '排名' 
    FROM 
        (SELECT score FROM t_salgray ORDER BY score DESC) AS s,
        (SELECT @paixu := 0 ,@rowtotal := NULL ,@incrnum := 0)r
    ) 
    AS w;
+-------+------+
| score | 排名 |
+-------+------+
|   500 |    1 |
|   400 |    2 |
|   400 |    2 |
|   300 |    4 |
|   200 |    5 |
|   100 |    6 |
|   100 |    6 |
+-------+------+
7 rows in set, 6 warnings (0.00 sec)

法二：rank() over

mysql> select score,rank() over(order by score desc) AS "rank" FROM t_salgray;
+-------+------+
| score | rank |
+-------+------+
|   500 |    1 |
|   400 |    2 |
|   400 |    2 |
|   300 |    4 |
|   200 |    5 |
|   100 |    6 |
|   100 |    6 |
+-------+------+
7 rows in set (0.01 sec)