MySQL implements window function [row_number() over(partition by xxx)] small example

Sometimes we need to group and sort the data, and our MySQL database version is lower than 8.0, and the windowing function cannot be used, so how should we achieve grouping and sorting?

Take a look at the sample data and the result of the sorting required:

The effect is grouped by name field, and login_time is sorted in reverse order. Reference code
 Use the following code to achieve row_number() over(partition by order by login_time desc)

Extension:   If you want to choose top N, you can filter the data with rank <N

The second effect is grouped by name and city fields, and login_time is sorted in reverse order. Reference code
Use the following code to achieve row_number() over(partition by name, city order by login_time desc)

Code collection:

-- 1、创建测试表drop table if exists tmp_rank_test;create table tmp_rank_test (  name varchar(255) ,    city varchar(255) ,  login_time datetime ); -- 2、插入测试数据insert into tmp_rank_test values ('Dina', '苏州市', '2021-01-27 09:26:50');insert into tmp_rank_test values ('Dina', '上海市', '2020-07-21 12:21:47');insert into tmp_rank_test values ('Dina', '徐州市', '2020-07-03 17:31:22');insert into tmp_rank_test values ('Dina', '上海市', '2020-05-29 14:05:56');insert into tmp_rank_test values ('Dina', '上海市', '2020-05-29 13:39:47');insert into tmp_rank_test values ('Dina', '上海市', '2020-05-26 11:27:25');insert into tmp_rank_test values ('Dina', '苏州市', '2020-02-17 14:34:25');insert into tmp_rank_test values ('Joyce', '徐州市', '2020-05-08 14:22:33');insert into tmp_rank_test values ('Joyce', '徐州市', '2020-04-10 20:51:54');insert into tmp_rank_test values ('Joyce', '苏州市', '2020-01-03 19:09:51');insert into tmp_rank_test values ('Paradox', '上海市', '2021-01-20 00:00:04');insert into tmp_rank_test values ('Paradox', '苏州市', '2020-04-01 10:00:04');insert into tmp_rank_test values ('Paradox', '扬州市', '2019-12-04 10:10:19');insert into tmp_rank_test values ('Paradox', '上海市', '2019-07-24 10:00:15'); -- 查看测试数据select * from tmp_rank_test;   -- 效果一 按 name 字段分组,login_time 倒序排序  参考代码--  用一下代码 实现 row_number() over(partition by name order by login_time desc)select     @num := IF(@name = name, @num + 1,1) rank_num 	, @name := name as name	,city	,login_timefrom  tmp_rank_test  torder by name, login_time desc;		 -- 扩展:  若想选 top N ,可 用 rank < N 来筛选数据select name,city,login_time,rank_num from (		select  			 @num := IF(@name = name, @num + 1,1) rank_num  			,@name := name as name			,login_time			,city		from  tmp_rank_test  t		order by name, login_time desc		)t where rank_num < 3;   -- 效果二 按 name、city 字段分组,login_time 倒序排序  参考代码-- 用以下代码实现 row_number() over(partition by name,city order by login_time desc)select    @num := if(@tmp_name = `name` and @tmp_city = city , @num := @num + 1, 1) as rank_num	,@tmp_name := name as name	,@tmp_city := city as city	,login_time		from tmp_rank_testorder by name,city,login_time desc;

Hope it can help you