小记:MySQL 实现 ROW_NUMBER() OVER (PARTITION BY ORDER BY ) 功能

最新推荐文章于 2024-06-28 03:39:01 发布

xiaolin_xinji

最新推荐文章于 2024-06-28 03:39:01 发布

阅读量1.8k

点赞数

分类专栏： MySql

本文链接：https://blog.csdn.net/weixin_44131414/article/details/107404659

版权

MySql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

需求:由于项目需求原因,需要从MySQL中获取人员最新在职信息,将查询出来的数据通过datax或kettle 工具,导入另一个业务库中,现发现MySQL中无ROW_NUMBER() OVER (PARTITION BY ORDER BY ) 函数,现记录下MySQL中实现该函数的sql
用户自定义变量的使用官方文档

mysql> SET @v1 = X'41';
mysql> SET @v2 = X'41'+0;
mysql> SET @v3 = CAST(X'41' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+
mysql> SET @v1 = b'1000001';
mysql> SET @v2 = b'1000001'+0;
mysql> SET @v3 = CAST(b'1000001' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

实现ROW_NUMBER() OVER (PARTITION BY ORDER BY ) 函数

		SELECT
				t.real_name,
				t.id_card_no,
				t.is_job,
				t.last_modify_time,
				if(@groups=t.id_card_no,@rank:=@rank+1,@rank:=1) as rank, --排序
				@groups:=t.id_card_no as tt_id_card_no  --分组
			FROM (	
					select
						
							t.real_name,
							t.id_card_no,
							t.is_job,
							t.last_modify_time
					from 
						labourer_resume t 
					where t.is_deleted =1 and t.source_pid != 0
					ORDER BY id_card_no,t.is_job DESC,t.last_modify_time DESC --先排序
			) t JOIN (SELECT @groups := null ,@rank:=0 ) r  --自定义两个变量用于分组 排序

查询结果

real_name  		id_card_no     is_job  	last_modify_time  	  rank 	 tt_id_card_no  
陈*忠		52212*****012012	1		2020-01-06 21:50:52		1	52212*****012012
陈*忠		52212*****012012	0		2019-11-09 11:26:48		2	52212*****012012
罗*江  		52212*****630361	0		2020-01-06 19:45:11		1	52212*****630361
罗*江 		52212*****630361	0		2019-10-30 10:53:48		2	52212*****630361