MySQL 实现row_number() over(partition by ) 分组排序功能

语法格式：

row_number() over(partition by 分组列 order by 排序列 desc)

 

row_number() over()分组排序功能：

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by，但不晚于 order by 的执行。

 

创建测试环境

在线数据库 http://sqlfiddle.com/

 

1. 创建表

-- 创建表
CREATE TABLE `a` (
	`ID` INT(10) NULL DEFAULT NULL,
	`class` INT(10) NULL DEFAULT NULL,
	`score` INT(10) NULL DEFAULT NULL
)COLLATE='utf8_general_ci';

-- 插入数据
insert into a values (1,1,90);
insert into a values (2,1,70);
insert into a values (3,1,90);
insert into a values (4,1,80);
insert into a values (5,2,100);
insert into a values (6,2,80);
insert into a values (7,2,110);
insert into a values (8,2,80);
insert into a values (9,2,80);
insert into a values (10,2,60);
commit;

 

2. Oracle row_number() over(partition by) 分组排序功能

select *
  from (select *,
               row_number() over(partition by class order by score desc) rn
          from a) b
 where rn = 1

 

3. MySQL自定义实现row_number() over(partition by) 分组排序功能

select id, class, score, rank
  from (select b.*,
        -- 定义用户变量@rownum来记录数据的行号。通过赋值语句@rownum := @rownum+1来累加达到递增行号。
               @rownum := @rownum + 1, 
        -- 如果当前分组编号和上一次分组编号相同，则@rank（对每一组的数据进行编号）值加1，否则表示为新的分组，从1开始
               if(@pdept = b.class, @rank := @rank + 1, @rank := 1) as rank, 
        -- 定义变量@pdept用来保存上一次的分组id
               @pdept := b.class 
        -- 这里的排序不确定是否需要，保险点还是加上吧
          from (select * from a order by a.class, a.score desc) b, 
        -- 初始化自定义变量值
               (select @rownum := 0, @pdept := null, @rank := 0) c 
        -- 该排序必须，否则结果会不对
         order by b.class, b.score desc 
        ) result
having rank < 2;

 

实例

Oracle

select app_xxzjbh, create_date, token_id, token_code, token_expire_time
  from (select g.app_xxzjbh,
               g.create_date,
               t.token_id,
               t.token_code,
               t.token_expire_time,
               (row_number()
                over(partition by g.app_xxzjbh order by t.create_date desc)) rn
          from RES_ESB_APP_TOKEN_GL g, RES_ESB_TOKEN t
         where g.token_xxzjbh = t.token_id) e
 where rn = 1

 

MySQL

select app_xxzjbh, token_id, token_code, token_expire_time, rank
  from (select app_xxzjbh,
               token_id,
               token_code,
               create_date,
               token_expire_time,
               @rownum := @rownum + 1,
               if(@pdept = app_xxzjbh, @rank := @rank + 1, @rank := 1) as rank, 
               @pdept := app_xxzjbh
          from (select g.app_xxzjbh,
                       g.create_date,
                       t.token_id,
                       t.token_code,
                       t.token_expire_time
                  from RES_ESB_APP_TOKEN_GL g, RES_ESB_TOKEN t
                 where g.token_xxzjbh = t.token_id) b,
               (select @rownum := 0, @pdept := null, @rank := 0) c
         order by b.app_xxzjbh, b.create_date desc
        ) result
having rank < 2;

 

参考

https://blog.csdn.net/a364901254/article/details/104382012