MySQL–ROW_NUMBER

一、窗口函数

要讲row_number，首先得讲一下窗口函数。窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

1）专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2）聚合函数，如sum. avg, count, max, min等

partition by用来对表分组。order by子句的功能是对分组后的结果进行排序。

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

二、row_number

在MySQL8.0版本中已支持row_number函数，接下来讲的内容基于MySQL5.7版本。

在8.0版本前无row_number函数，那么我们可以自己建一个类似row_number的功能。

在这里插入图片描述

以上为表TEMP_TEST的数据。

要模拟row_number（）函数，必须使用会话变量。

1、每行添加序号

SET @row_number = 0;
SELECT( @row_number := @row_number + 1 ) AS ROW_NUMBER,ID,NAME,AGE,ADDRESS 
from TEMP_TEST
ORDER by AGE;
-- 新写法
SELECT( @row_number := @row_number + 1 ) AS ROW_NUMBER,ID,NAME,AGE,ADDRESS 
from TEMP_TEST a,(select @row_number := 0) b
ORDER by AGE;

在这里插入图片描述

2、每组添加序号

-- row_number()
SELECTROW_NUMBER() OVER(PARTITION BY AGE ORDER BY ID),ID,NAME,AGE,ADDRESS
FROMTEMP_TEST; -- 自定义
SELECT@row_number :=-- 若AGE相等则自增，若partition有多个字段则可以使用多个变量进行判断，如：@AGE = AGE and @ADDRESS = ADDRESSIF( @AGE = AGE, @row_number + 1, 1 ) AS ROW_NUMBER, @AGE := AGE AS AGE, -- 赋值partition字段ID,NAME,AGE,ADDRESS
FROMTEMP_TEST 
ORDER BYAGE,ID DESC; -- 其它示例
select ROW_NUMBER() OVER(PARTITION BY pid,checked ORDER BY id) t , a.* from t_test_area a;
select @row_number := if(@pid = pid and @checked = checked, @row_number + 1, 1 ) row_number,@pid := pid, @checked := checked, t.*
from t_test_area t
order by pid,checked,id;

在这里插入图片描述

三、视图中使用row_number（）

	可参考：https://blog.csdn.net/weixin_45873444/article/details/119895410

我在做Oracle向MySQL的数据库迁移中遇到了视图中使用row_number（）函数的情况，一起看一下怎么在MySQL中用上面的方法实现视图中的row_number（）函数。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gFJGOLpf-1620808711750)(C:\Users\yss\AppData\Roaming\Typora\typora-user-images\image-20210510174119337.png)]$

如果直接使用上面的方法会直接报错，这是因为MySQL在视图中不支持使用会话变量，所以我们只能创建一个函数来替代使用。

-- 一般写法
create function row_number()
returns INTEGER 
return @row_number := @row_number + 1;
-- 带复位的写法
CREATE FUNCTION func_row_num(reset bit) RETURNS int(11)
beginif reset thenset @row_num := 0;elseset @row_num := ifnull(@row_num, 0) + 1;end if;return  @row_num;
end