MySQL系列之实现Oracle rank()排序

SmileNicky

发布于 2022-05-07 17:43:36

5500

发布于 2022-05-07 17:43:36

文章被收录于专栏：Nicky's blog

一、Oracle写法介绍

MySQL5.7版本没有提供类似Oracle的分析函数，比如开窗函数over(…)，oracle开窗函数over(…)使用的话一般是和order、partition by、row_number()、rank()、dense_rank()几个函数一起使用，具体的用法可以参考我之前的博客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，就用学生成绩排名的例子

学号	姓名	班级	成绩
111	小王	1	92
123	小李	2	90
134	小钱	3	92
145	小顺	4	100

数据表为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 ;

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

用执行计划来解释：

加上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的主查询

④、同理，主查询2，衍生查询SELECT stuId, stuName,classId,score FROM t_score ORDER BY score ASC查询成功后，在进行外面的主查询，也就是对别名为c的主表查询

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

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

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

MySQL实现的效果：

Oracle实现的效果：

很显然，如图如比对所示，在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 ;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019-09-19，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle