【MySQL】MySQL5.7实现MySQL8的排序窗口函数

最新推荐文章于 2024-04-18 10:43:52 发布

wheelmouse7788

最新推荐文章于 2024-04-18 10:43:52 发布

阅读量612

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/whereabouts_/article/details/133785716

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL8三种常用的排序函数：

row_number()：在每个分组内，为查询出来的每一行记录生成一个序号，依次排序且不会重复（即使结果相同，也会排出1,2,3 名）
rank()：在每个分组内，如果有两个第一时，接下来就是第三
dense_rank()：在每个分组内,如果有两个第一时，接下来仍然是第二

准备样例数据

-- 建表
create table t (
    id int(11) not null auto_increment primary key,
    name varchar(15),
    salary decimal(10,2)
);
-- 插入数据
insert into 
	t(name, salary)
values 
    ('Lebron', 15000),
    ('Jokic', 10000),
    ('Giannis', 15000),
    ('Paul', 9000),
    ('Doncic', 7000);

MySQL8窗口函数效果
row_number()

select
       name,
       salary,
       row_number() over (partition by salary order by salary desc) as ranking
from t;

row_number()
rank()

select
       name,
       salary,
       rank() over (order by salary desc) as ranking
from t;

rank()

select
       name,
       salary,
       dense_rank() over (order by salary desc) as ranking
from t;

在这里插入图片描述

以上函数只有MySQL版本为8.0以上才支持，如果是MySQL5.7应该如何实现呢？
row_number()

SELECT
    id,
    name,
    salary,
    @rn := @rn + 1 AS rn -- @rn 记录当前行号
FROM
    t,
    (SELECT @prev := NULL, @curr := NULL, @first := 1, @rn := 0) var
ORDER BY salary DESC;

rank()

-- 主要逻辑:
-- 定义变量@prev、@curr跟踪当前分数和前一个分数
-- 定义变量@rank跟踪排名
-- 当@prev与@curr不同时,@rank增加1
-- 当@prev与@curr相同时,@rank保持不变
-- 这样,相同分数的记录会获得相同排名,不同分数的记录按顺序获得新增的排名。
SELECT
    id,
    name,
    salary, 
    @rn := @rn + 1 AS rn, -- @rn 记录当前行号
    @prev := @curr,
    @curr := salary,
    @first := IF(@prev <> @curr, @rn, @first),  -- @first 记录当前分数首次出现的行号作为基准排名，当分数变更时,更新@first为当前行号
    @rank := @first AS ranknum -- 排名 @rank 直接取值为 @first
FROM
    t,
    (SELECT @prev := NULL, @curr := NULL, @first := 1, @rn := 0) var
ORDER BY salary DESC;

dense_rank()

SELECT
    id,
    name,
    salary,
    @rn := @rn + 1 AS rn, -- @rn 记录当前行号
    @prev := @curr,
    @curr := salary,
    @first := IF(@prev <> @curr, @first + 1, @first),  -- @first 记录当前分数首次出现的行号作为基准排名，当分数变更时,更新@first为当前行号
    @rank := @first AS ranknum -- 排名 @rank 直接取值为 @first
FROM
    t,
    (SELECT @prev := NULL, @curr := NULL, @first := 1, @rn := 0) var
ORDER BY salary DESC;