mysql5模拟oracle的row_number、dense_rank、rank功能

最新推荐文章于 2023-08-26 09:58:27 发布

daodfs1

最新推荐文章于 2023-08-26 09:58:27 发布

阅读量301

点赞数

分类专栏： sql 文章标签： mysql sql

本文链接：https://blog.csdn.net/daodfs111/article/details/112085503

版权

sql 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

功能说明

row_number() 是连续不重复的排序，可以利用它来实现分页，例如1，2，3，4，5，6
dense_rank() 是连续排序，多个第二名仍然跟着第三名，例如1，2，2，3，4，5
rank() 是跳跃排序，两个第一名下来就是第三名，三个第三名下来就是第六名，例如1，1，3，3，3，6

mysql8.0已原生支持，本篇文章仅会对mysql5.7及以前的版本

测试sql数据

CREATE TABLE g_stud (
  id varchar(30) NOT NULL,
  username varchar(30) NOT NULL,
  userscore int(11),
  usersubject varchar(30),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into g_stud (id, username, userscore, usersubject) values('1001','张三','98','语文');
insert into g_stud (id, username, userscore, usersubject) values('1002','张三','86','数学');
insert into g_stud (id, username, userscore, usersubject) values('1003','张三','86','英语');
insert into g_stud (id, username, userscore, usersubject) values('1004','张三','73','物理');
insert into g_stud (id, username, userscore, usersubject) values('1005','李四','85','语文');
insert into g_stud (id, username, userscore, usersubject) values('1006','李四','92','数学');
insert into g_stud (id, username, userscore, usersubject) values('1007','李四','79','英语');
insert into g_stud (id, username, userscore, usersubject) values('1008','李四','79','物理');
insert into g_stud (id, username, userscore, usersubject) values('1009','王五','68','语文');
insert into g_stud (id, username, userscore, usersubject) values('1010','王五','79','数学');
insert into g_stud (id, username, userscore, usersubject) values('1011','王五','92','英语');
insert into g_stud (id, username, userscore, usersubject) values('1012','王五','92','物理');

无分组row_number

SELECT
  s.id, s.username, s.userscore, s.usersubject, s.rn
FROM (
  SELECT
    s.id, s.username, s.userscore, s.usersubject,
    (@rownum:=@rownum + 1) rn
  FROM
    (SELECT id, username, userscore, usersubject FROM g_stud ORDER BY userscore desc, id) s,
    (select @rownum:=0) r
 ) s

无分组dense_rank

SELECT
  s.id, s.username, s.userscore, s.usersubject, s.rn
FROM (
  SELECT
    s.id, s.username, s.userscore, s.usersubject,
    if(@sc=s.userscore, @dense_rank:=@dense_rank, @dense_rank:=@dense_rank + 1) rn,
    @sc:=s.userscore
  FROM
    (SELECT id, username, userscore, usersubject FROM g_stud ORDER BY userscore desc, id) s,
    (select @sc:=null, @dense_rank:=0) r
) s

无分组rank

SELECT
  s.id, s.username, s.userscore, s.usersubject, s.rn
FROM (
  SELECT
    s.id, s.username, s.userscore, s.usersubject,
    @rownum:=@rownum + 1,
    if(@sc=s.userscore, @rank:=@rank, @rank:=@rownum) rn,
    @sc:=s.userscore
  FROM
    (SELECT id, username, userscore, usersubject FROM g_stud ORDER BY userscore desc, id) s,
    (select @sc:=null, @rownum:=0, @rank:=0) r
) s

分组row_number

SELECT
  s.id, s.username, s.userscore, s.usersubject, s.rn
FROM (
  SELECT
    s.id, s.username, s.userscore, s.usersubject,
    if(@uname=s.username, @rownum:=@rownum + 1, @rownum:=1) rn,
    @uname:=s.username
  FROM
    (SELECT id, username, userscore, usersubject FROM g_stud ORDER BY username, userscore desc, id) s,
    (select @uname:=null, @rownum:=0) r
) s

分组dense_rank

SELECT
  s.id, s.username, s.userscore, s.usersubject, s.rn
FROM (
  SELECT
    s.id, s.username, s.userscore, s.usersubject,
    if(@uname=s.username, if(@sc=s.userscore, @dense_rank:=@dense_rank, @dense_rank:=@dense_rank + 1), @dense_rank:=1) rn,
    @uname:=s.username,
    @sc:=s.userscore
  FROM
    (SELECT id, username, userscore, usersubject FROM g_stud ORDER BY username, userscore desc, id) s,
    (select @uname:=null, @sc:=null, @dense_rank:=0) r
) s

分组rank

SELECT
  s.id, s.username, s.userscore, s.usersubject, s.rn
FROM (
  SELECT
    s.id, s.username, s.userscore, s.usersubject,
    if(@uname=s.username, @rownum:=@rownum + 1, @rownum:=1),
    if(@sc=s.userscore, @rank:=@rank, @rank:=@rownum) rn,
    @uname:=s.username,
    @sc:=s.userscore
  FROM
    (SELECT id, username, userscore, usersubject FROM g_stud ORDER BY username, userscore desc, id) s,
    (select @uname:=null, @sc:=null, @rownum:=0, @rank:=0) r
) s

注意事项

必须先对数据order by, 并且order by字段必须确定唯一的行，否则mysql可能排序失效。所以order by最后一个字段加了个id
注意select子句变量计算列的先后顺序，因为select子句的字段是按从左到右执行的，否则rn列的查询结果必然错误。所以顺序为@rownum、（@dense_rank 或 @rank）、（@uname | @sc），其中@uname、@sc顺序可以互换。当然，变量初始化顺序是可以随意的。
注意：if函数的第一个参数@uname=s.username或@sc=s.userscore是没有冒号的，有冒号的是赋值语句