MySQL系列之实现Oracle rank()排序
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 ;