MySQL–ROW

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()
SELECT
	ROW_NUMBER() OVER(PARTITION BY AGE ORDER BY ID),
	ID,NAME,AGE,ADDRESS
FROM
	TEMP_TEST; 
	
-- 自定义
SELECT
	@row_number :=
	-- 若AGE相等则自增，若partition有多个字段则可以使用多个变量进行判断，如：@AGE = AGE and @ADDRESS = ADDRESS
	IF( @AGE = AGE, @row_number + 1, 1 ) AS ROW_NUMBER, 
	@AGE := AGE AS AGE, -- 赋值partition字段
	ID,NAME,AGE,ADDRESS
FROM
	TEMP_TEST 
ORDER BY
	AGE,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（）函数。

​

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

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

在视图中使用自建的函数即可实现视图中使用row_number（）函数。