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;

  

 

  
1
  
2
  
3
  
4
  
5
 

二、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);


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
 

然后给出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 ;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
 

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

用执行计划来解释：

加上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 ;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
 

文章来源: smilenicky.blog.csdn.net，作者：smileNicky，版权归原作者所有，如需转载，请联系作者。

原文链接：smilenicky.blog.csdn.net/article/details/100898845