MySQL应用之实现Oracle rank()排序

我就是我 2021-08-19 13:26 380阅读 0赞

## 一、Oracle写法介绍 ##

MySQL5.7版本没有提供类似Oracle的分析函数，比如开窗函数over(…)，oracle开窗函数over(…)使用的话一般是和order、partition by、row\_number()、rank()、dense\_rank()几个函数一起使用，具体的用法可以参考我之前的博客[oracle开窗函数用法简介][oracle]

假如要获取成绩排序第一的学生信息，可以用如下的SQL：

select *
      from (select stuId, stuName, classId,
                   row_number() over(partition by classId order by score desc) rn
              from t_score)
     where rn = 1;

## 二、Oracle和MySQL写法对比 ##

ok，就用学生成绩排名的例子

数据表为t\_score，字段分别为stuId，stuName，classId ，score

环境准备，先建表，写数据：

#成绩表
    CREATE TABLE t_score(
       stuId VARCHAR(20),
       stuName VARCHAR(50),
       classId INT,
       score FLOAT
    );
    # 写数据
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('111','小王',2,92);
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('123','小李',1,90);
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('134','小钱',1,92);
    INSERT INTO t_score(stuId,stuName,classId,score) VALUES('145','小顺',2,100);

然后给出sql，用的是临时变量的方法：

SELECT 
      IF(
        @classId := c.classId,
        @rn := @rn + 1,
        @rn := 1
      ) AS rn,
      c.stuId,
      c.stuName,
      c.classId,
      c.score ,
      @classId := c.classId
    FROM
      (SELECT 
        stuId,
        stuName,
        classId,
        score 
      FROM
        t_score 
      ORDER BY score ASC) c,
      (SELECT 
        @rn := 0,
        @classId := NULL) r ;

不过对于上面的写法，这里也进行分析，让学习者可以更好理解，因为很多地方都是直接贴代码，不写明原因，对于入门者来说，可能都不理解

用执行计划来解释：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

加上Explain,对于执行计划不熟悉的学习者可以参考我之前博客：[MySQL Explain学习笔记][MySQL Explain]

从执行计划可以看出：

*  ①、上面SQL，执行时候是先执行这条衍生查询SQL，`SELECT @rn := 0,@classId := NULL`，这个其实是为了初始化临时变量@rn和@classId
 *  ②、执行查询t\_score,`SELECT stuId, stuName,classId,score FROM t_score ORDER BY score ASC`，同样是返回一个衍生表
 *  ③、主查询1，`SELECT @rn := 0,@classId := NULL`衍生查询完成后，进行别名为r的主查询  
    ![在这里插入图片描述][20190917103419333.png]
 *  ④、同理，主查询2，衍生查询`SELECT stuId, stuName,classId,score FROM t_score ORDER BY score ASC`查询成功后，在进行外面的主查询，也就是对别名为c的主表查询  
    ![在这里插入图片描述][20190917103728253.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]

所以网上这种写法也是值得学习的，一种是利用了mysql的执行计划执行顺序对临时变量进行赋值，然后再用临时变量进行叠加，写法还是值得学习的

对于临时变量的知识点，可以参考我之前博客：[MySQL变量学习笔记][MySQL]

注意：这里网上有很多这种写法，不过我验证了，并不能实现了oracle类似的partition by效果，也就是没分组效果，也有可能是哪里写错了，欢迎指出！

MySQL实现的效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]

Oracle实现的效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]

很显然，如图如比对所示，在oracle里，不仅分组了，而且rn也按照班级进行排名，Oracle实现的效果显然和网上很多地方提出的这种写法效果是不一样的，网上的这种写法仅仅是进行排序而已，并没有按照班级进行分组排名

上面都是自己动手验证过，目的是指出网上很多地方的这种写法是不正确的，或许也有可能是自己写错哪里了，都欢迎指出！

所以，对于Oracle rank()、row\_number加上开窗函数进行排序，并没有partition by分组的时候，是可以用这种方法，不过写法要改一下，代码如：

SELECT 
     /* IF(
        @classId := c.classId
        AND @score := c.score,
        @rn := @rn + 1,
        @rn := 1
      ) AS rn,*/
     rn := @rn+1 as rn,
      c.stuId,
      c.stuName,
      c.classId,
      c.score ,
      @classId := c.classId
    FROM
      (SELECT 
        stuId,
        stuName,
        classId,
        score 
      FROM
        t_score 
      ORDER BY score ASC,classId) c,
      (SELECT 
        @rn := 0,
        @classId := NULL) r ;

[oracle]: https://smilenicky.blog.csdn.net/article/details/85412064
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/fd57ff69fbae4ed0b28919248fb3f55c.png
[MySQL Explain]: https://smilenicky.blog.csdn.net/article/details/100853310
[20190917103419333.png]: /images/20210813/67a168c420ea406bb0a35ed9e3376aac.png
[20190917103728253.png]: /images/20210813/57f111fa37d4461f93418f069f59e4fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210813/3c5a20cd3a6241d3b9d3dc95914e2027.png
[MySQL]: https://smilenicky.blog.csdn.net/article/details/100889674
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210813/13694bc20c994ecfbb578ede3f270af1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: /images/20210813/050f0f3080c749e796e7d5ef848e61ec.png