Mysql @RN :方式ROW_NUMBER的实现方法

1简介

基于mysql @rownum:=@rownum+1的方式模拟实现row_number

2 准备

2.1 环境说明

1 mysql版本10.3.13-MariaDB,同理其它mysql版本效果类似。

-- 查看mysql版本
SELECT VERSION() AS mysql_Version;

mysql_Version

10.3.13-MariaDB

-- mysql 字符集
show variables like '%char%';

Variable_name	Value
character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results
character_set_server	utf8
character_set_system	utf8
character_sets_dir	E:\Program Files\MariaDB 10.3\share\charsets\

3 安装

无.

4 配置

无

5 使用

5.1 验证实验

1) mysql里创建一个emp_t10表定义empid ,deptid ,salary字段的类型，插入如下10条数据验证。

-- 1 创建表并插入数据
drop table if exists emp_t10; 
create table emp_t10 (empid int ,deptid int ,salary decimal(10,2) );
 insert into emp_t10 values  
(1,10,5500.00), 
(2,10,4500.00), 
(3,20,1900.00), 
(4,20,4800.00), 
(5,40,6500.00), 
(6,40,14500.00), 
(7,40,44500.00), 
(8,50,6500.00), 
(9,50,7500.00),
 (10, 20,1900.00 );

-- 2 将按照部门升序、工资降序的数据插入临时表中
create table emp_t10_tmp AS select empid,deptid,salary from emp_t10 order by deptid asc ,salary desc; 


-- 3 按照deptid分组,按照salary降序排名
select empid,deptid,salary,rank from ( 
  select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum := @rownum+1 , 
  if(@pdept= heyf_tmp.deptid,@rank:=@rank +1, @rank:= 1) as rank, 
  @pdept:=heyf_tmp.deptid 
  from (  
  -- select empid,deptid,salary from emp_t10 order by deptid asc ,salary desc  
  select empid,deptid,salary from emp_t10_tmp
  ) heyf_tmp ,
  (select @rownum:=0 , @pdept:= null ,@rank:= 0) a 
) result 
order by deptid,rank;

-- 4 建议的写法,按照deptid分组,按照salary降序排名(这里没用利用临时表,order by 在两个表cross join之后)
select empid,deptid,salary,rank from ( 
  select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum := @rownum+1 , 
  if(@pdept= heyf_tmp.deptid,@rank:=@rank +1, @rank:= 1) as rank, 
  @pdept:=heyf_tmp.deptid 
  from (  
  select empid,deptid,salary from emp_t10 -- order by deptid asc ,salary desc  
  -- select empid,deptid,salary from emp_t10_tmp
  ) heyf_tmp ,
  (select @rownum:=0 , @pdept:= null ,@rank:= 0) a 
  order by deptid asc ,salary desc
) result 
order by deptid,rank;

empid	deptid	salary	rank
1	10	5500.00	1
2	10	4500.00	2
10	20	1900.00	2
3	20	1900.00	3
4	20	4800.00	1
7	40	44500.00	1
6	40	14500.00	2
5	40	6500.00	3
8	50	6500.00	2
9	50	7500.00	1

2) 当然在当前的MariaDB 版本里已经支持了ROW_NUMER,所以我们可以直接按照如下的语法实现想要的需求：

select empid,deptid,salary,
ROW_NUMBER() OVER(PARTITION BY deptid ORDER BY salary DESC)rank  
from emp_t10;

empid	deptid	salary	rank
1	10	5500.00	1
2	10	4500.00	2
4	20	4800.00	1
10	20	1900.00	2
3	20	1900.00	3
7	40	44500.00	1
6	40	14500.00	2
5	40	6500.00	3
9	50	7500.00	1
8	50	6500.00	2

5.2 问题分析

无

6 延展

无