mysql 7 中实现 row_number /rank/dense_rank

傷城~ 2021-09-28 13:42 385阅读 0赞

关于row\_number/rank/dense\_rank之间的区别  
[https://blog.csdn.net/nayi\_224/article/details/79862283][https_blog.csdn.net_nayi_224_article_details_79862283]

mysql版本5.7.23-log

create table test_190507(part VARCHAR(50), val1 NUMERIC, val2 NUMERIC);
    
    insert into test_190507(part, val1, val2) values (‘a’, 1, 4);
    insert into test_190507(part, val1, val2) values (‘a’, 2, 3);
    insert into test_190507(part, val1, val2) values (‘a’, 3, 2);
    insert into test_190507(part, val1, val2) values (‘a’, 4, 1);
    insert into test_190507(part, val1, val2) values (‘b’, 1, 4);
    insert into test_190507(part, val1, val2) values (‘b’, 1, 3);
    insert into test_190507(part, val1, val2) values (‘b’, 2, 2);

![在这里插入图片描述][20190507141722880.png]

### row\_number ###

现在要实现类似oracle中的`row_number() over(partition by part order by val1, val2 desc)` 的功能。

select t1.*,
    			 @row_number := case when @temp = t1.part then @row_number + 1 else 1 end rn,
    			 @temp := t1.part tmp
      from 
    (select * from test_190507 t1 order by t1.part, t1.val1, t1.val2 desc) t1,
    (select @row_number := 1 row_number, @temp := '' temp) t2
    ;

![在这里插入图片描述][20190507141954688.png]  
其中rn即为结果列。

主要利用了结果集的排序以及语句的执行顺序来依次的设置变量，很基础的for循环思维。但是由于依赖了底层结果集的排序，导致一次只能生成一个排序列。在oracle中，row\_number函数很可能是成批出现的，并且有完全不同的排序规则。针对这种情况，我暂时没找到了好方法。多写几个然后在关联吧。

### dense\_rank ###

select t1.*,
    			 @dense_rank := case when @lag_part != t1.part then 1 when @lag_dense = t1.val1 then @dense_rank else @dense_rank + 1 end dr,
    			 @lag_part := t1.part lag_part,
    			 @lag_dense := t1.val1 lag_dense
      from 
    (select * from test_190507 t1 order by t1.part, t1.val1) t1,
    (select @dense_rank := 1 , @lag_part := '' , @lag_dense := '' ) t2
    ;

dr是结果列  
![在这里插入图片描述][20190507145747302.png]

### rank ###

select t1.part,
    	   t1.val1,
    	   t1.rank
      from (
    select t1.part,
    			 t1.val1,
    			 @row_number := case when @lag_part = t1.part then @row_number + 1  else 1 end tmp_rn,
    			 @dense_rank := case when @lag_part != t1.part then 1 when @lag_flag = t1.val1 then @rank else @row_number end rank,
    			 @rank := case when @lag_part != t1.part then 1 when @lag_flag = t1.val1 then @rank else @row_number end tmp_rk,
    			 @lag_part := t1.part lag_part,
    			 @lag_flag := t1.val1 lag_flag
      from 
    (select * from test_190507 t1 order by t1.part, t1.val1) t1,
    (select @row_number := 1, @lag_part := '' , @lag_flag := '', @rank := 1 ) t2
    ) t1
    ;

![在这里插入图片描述][20190507151258548.png]  
听说mysql8添加了不少语法，已经支持分析函数了，甚至连递归with都支持了，尽量快点升级版本吧，这东西写着太烦。

[https_blog.csdn.net_nayi_224_article_details_79862283]: https://blog.csdn.net/nayi_224/article/details/79862283
[20190507141722880.png]: /images/20210923/95291a6cb0974426bc18786a2c31b4e4.png
[20190507141954688.png]: /images/20210923/a66b89c0a8754918966a6c59b4fbe08c.png
[20190507145747302.png]: /images/20210923/d75ed111447c4e009e81562de57aba60.png
[20190507151258548.png]: /images/20210923/a45e5a5374a942f78b1f04957fd7de18.png