黑马程序员技术交流社区

标题: 【西安校区】MySQL实现Oracle rank()排序 [打印本页]

---

作者: 逆风TO    时间: 2019-9-26 14:35
标题: 【西安校区】MySQL实现Oracle rank()排序
一、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 ;

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2