• row_number()/rank() over(PARTITION BY xxx ORDER BY的MySQL5.7实现


    背景说明

    oracle、mysql8.0以上版本有ROW_NUMBER() OVER (PARTITION BY) 函数可以进行分组并进行组内排序,

    但是5.7以下版本是没有这个函数,我们这时候可以利用临时变量来实现这个效果。

    测试表数据:test1

    • CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_test` /*!40100 DEFAULT CHARACTER SET utf8 */;

    • USE `db_test`; /*Table structure for table `test1` */

    • DROP TABLE IF EXISTS `test1`;

    • CREATE TABLE `test1` ( `id` int(10) NOT NULL, `score` int(20) DEFAULT NULL, `class` char(10) COLLATE utf8_bin DEFAULT NULL, `name` char(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    • /*Data for the table `test1` */

    • insert into `test1`(`id`,`score`,`class`,`name`) values (1,100,'语文','张三'),(2,98,'语文','李四'),(3,98,'语文','王五'),(4,98,'数学','张三'),(5,96,'数学','李四'),(6,92,'数学','王五'),(7,85,'数学','张三'),(8,96,'语文','张三'),(9,96,'语文','张三'),(10,91,'语文','张三'),(11,91,NULL,'张三');

    rank()和row_number()的区别

    • row_number():当分数相同的时候还会依次进行排序,排名不同。
    • rank():当分数相同的时候不会依次排序,相同的分数排名相同。

     row_number():

    rank():

    -- 百度示例


    #使用开窗函数,兼容oracle
    SELECT * FROM (
      SELECT *,row_number() over(PARTITION BY class ORDER BY score DESC) mm FROM  a
    ) b WHERE mm=1

    #自定义实现row_number() over(PARTITION BY class ORDER BY    )
    SELECT id,class,score,rank FROM (
     SELECT 
      b.*,
      @rownum := @rownum+1 ,-- 定义用户变量@rownum来记录数据的行号。通过赋值语句@rownum := @rownum+1来累加达到递增行号。
      IF(@pdept=b.class,@rank:=@rank+1,@rank:=1) AS rank,-- 如果当前分组编号和上一次分组编号相同,则@rank(对每一组的数据进行编号)值加1,否则表示为新的分组,从1开始
      @pdept:=b.class -- 定义变量@pdept用来保存上一次的分组id
     FROM (SELECT * FROM test1 a ORDER BY a.class,a.score DESC) b ,-- 这里的排序不确定是否需要,保险点还是加上吧
      (SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) c  -- 初始化自定义变量值
     ORDER BY b.class,b.score DESC -- 该排序必须,否则结果会不对
    ) result
    HAVING rank < 2;


     单个分组依据:PARTITION BY后面跟一个

    1. -- MySQL8语法
    2. -- row_number()
    3. SELECT T.*,
    4. ROW_NUMBER() OVER(PARTITION BY T.class ORDER BY T.score DESC) RNK FROM test1 T
    5. -- rank()
    6. SELECT T.*,
    7. RANK() OVER(PARTITION BY T.class ORDER BY T.score DESC) RNK FROM test1 T
    8. -- MySQL5语法,8不能用,会报错
    9. -- row_number() desc降序
    10. SELECT
    11. a.*,
    12. @rownum := @rownum+1 ,
    13. IF(@class=a.class ,@rank:=@rank+1,@rank:=1) AS rank,
    14. @class:=a.class
    15. FROM test1 a,
    16. (SELECT @rownum :=0 , @class := NULL , @score:=NULL, @rank:=0) b
    17. ORDER BY a.class DESC, a.score DESC;
    18. -- row_number() asc升序,默认
    19. SELECT
    20. a.*,
    21. @rownum := @rownum+1 ,
    22. IF(@class=a.class ,@rank:=@rank+1,@rank:=1) AS rank,
    23. @class:=a.class
    24. FROM test1 a,
    25. (SELECT @rownum :=0 , @class := NULL , @score:=NULL, @rank:=0) b
    26. ORDER BY a.class ASC, a.score ASC;
    27. -- MySQL5语法,8不能用,会报错
    28. -- rank() desc
    29. SELECT
    30. a.*,
    31. @rownum := @rownum+1 ,
    32. IF(@class=a.class ,@rank1:=@rank1+1,@rank1:=1) AS rank1, #为了拿到rank1参数,给下面一行使用
    33. IF(@class=a.class,@rank:=(IF(@score=a.score, @rank, @rank1)),@rank:=1) AS rank,
    34. @class:=a.class,
    35. @score:=a.score
    36. FROM test1 a,
    37. (SELECT @rownum :=0 , @class := NULL , @score:=NULL, @rank:=0) b
    38. ORDER BY a.class DESC, a.score DESC; #partition by谁,就放在这里order by的后面第一位
    39. -- MySQL5语法,8不能用,会报错
    40. -- rank() asc
    41. SELECT
    42. a.*,
    43. @rownum := @rownum+1 ,
    44. IF(@class=a.class ,@rank1:=@rank1+1,@rank1:=1) AS rank1, #为了拿到rank1参数,给下面一行使用
    45. IF(@class=a.class,@rank:=(IF(@score=a.score, @rank, @rank1)),@rank:=1) AS rank,
    46. @class:=a.class,
    47. @score:=a.score
    48. FROM test1 a,
    49. (SELECT @rownum :=0 , @class := NULL , @score:=NULL, @rank:=0, @rank1:=0) b
    50. ORDER BY a.class ASC, a.score ASC; #partition by谁,就放在这里order by的后面第一位

    多个分组依据:PARTITION BY后面跟2个 

    1. -- MySQL8语法
    2. -- row_number()
    3. SELECT T.*,
    4. ROW_NUMBER() OVER(PARTITION BY T.class, T.name ORDER BY T.score DESC) RNK FROM test1 T #order by后面只是最后排序,不影响ran
    5. -- rank()
    6. SELECT T.*,
    7. RANK() OVER(PARTITION BY T.class, T.name ORDER BY T.score DESC) RNK FROM test1 T
    8. -- MySQL5语法,8不能用,会报错
    9. -- row_number() desc降序
    10. SELECT
    11. a.*,
    12. @rownum := @rownum+1 ,
    13. IF(@class<=>a.class AND @name=a.name,@rank:=@rank+1,@rank:=1) AS rank,
    14. @class:=a.class,
    15. @name:=a.name
    16. FROM test1 a,
    17. (SELECT @rownum :=0 , @rank:=0, @class := NULL , @score:=NULL, @name:=NULL) b
    18. ORDER BY a.class DESC, a.name DESC, a.score DESC;
    19. -- MySQL5语法,8不能用,会报错
    20. -- rank() desc降序
    21. SELECT
    22. a.*,
    23. @rownum := @rownum+1 ,
    24. IF(@class<=>a.class AND @name<=>a.name,@rank1:=@rank1+1,@rank1:=1) AS rank1, #为了拿到rank1参数,给下面一行使用
    25. IF(@class<=>a.class AND @name<=>a.name,@rank:=(IF(@score<=>a.score, @rank, @rank1)),@rank:=1) AS rank,
    26. @class:=a.class,
    27. @name:=a.name,
    28. @score:=a.score
    29. FROM test1 a,
    30. (SELECT @rownum :=0 , @rank:=0, @class := NULL , @score:=NULL, @name:=NULL) b
    31. ORDER BY a.class DESC, a.name DESC, a.score DESC;

     注意:

    1、当score没有相同的,那么rank()和row_number()效果一样
    2、如果表数据中有null,则判断的时候需要用 <=>来判断是否想等,因为null不能用=判断      select null = null    输出为null,不对,select null <=> null才返回1,对的  

       

    参考资料:

    mysql5.7 实现分组后组内排序功能 ROW_NUMBER() OVER (PARTITION BY)_普通网友的博客-CSDN博客_mysql5.7 分组排序

  • 相关阅读:
    Kubernetes网络场景分析
    2022春季《人工智能》EOJ代码个人汇总(A.八数码问题 到 J.迷宫寻找)
    TRC天然拟胆碱生物碱丨艾美捷TRC盐酸乙环胺
    全站最简单 “数据滚动可视化大屏” 【JS基础拿来即用】
    Flink1.15源码解析--选举
    C语言:二维数组的传递
    Attention Transformer
    科林Linux7_网络爬虫
    【云原生】Kubernetes介绍
    C++:封装,继承
  • 原文地址:https://blog.csdn.net/weixin_43211480/article/details/127590617