MySQL序号函数的区别

目录

一、序号函数相关说明

二、函数基本用法及效果演示

1）row_number()

2）rank()

3）dense_rank()

三、适用场景及情景演示

---

为啥写这篇笔记？因为之前用的版本是5.7的，呜呜呜针对排序的情况数据写不出来浪费一大堆时间，所以一看到这个好东西立马分享啦。废话不多说，看下面~

一、序号函数相关说明

1、MySQL从8.0开始支持窗口函数，之前的版本不支持需注意！！！

2、序号函数又称排名函数，是窗口函数的功能之一：row_number()、rank() 、dense_rank()

3、序号函数之间的差别

1）row_number()，默认按升序进行排序，无论是否出现重复值

        排序：1、2、3······

2）rank()，默认按升序进行排序，对于重复值并列排序，同时序号往后+n（n:重复的次数）

        排序：1、1、3······

3）dense_rank()，默认按升序进行排序，对于重复值并列排序，但序号仍依次递增

        排序：1、1、2······

二、函数基本用法及效果演示

1）row_number()

row_number() over ( partition by ... order by ... ) 
-- partition by：根据什么分组，类似group by
-- order by：根据什么排序，默认升序

2）rank()

rank() over ( partition by ... order by ... ) 
-- partition by：根据什么分组，类似group by
-- order by：根据什么排序，默认升序

3）dense_rank()

dense_rank() over (partition by ... order by ... ) 
-- partition by：根据什么分组，类似group by
-- order by：根据什么排序，默认升序

三、适用场景及情景演示

1、求每个班级数学成绩最高的学生，若最高分数出现重复，则分别取出学生信息

2、思路：

1）使用序号函数查找，对班级进行分组

2）题目要求若最高分数出现重复，分别取出学生信息，所以这边使用rank()或dense_rank()查找都能满足题目效果；不使用row_number()是因为出现最高分数重复时无法分别取出学生信息、

3、参考答案及效果演示：

-- 方法一：RANK()
SELECT class,students,km,fs
from (
SELECT *,
			RANK() over(PARTITION by class order by fs desc) rn
from classa
where km='数学'
			) a
where rn=1;		

-- 方法一：DENSE_RANK()
SELECT class,students,km,fs
from (
SELECT *,
			DENSE_RANK() over(PARTITION by class order by fs desc) rn
from classa
where km='数学'
			) a
where rn=1;

4、示例表数据创建

-- 创建示例表
CREATE TABLE `classa`  (
  `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `students` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `km` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `fs` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- 插入数据
INSERT INTO `classa` VALUES ('一班', '小红', '数学', 90);
INSERT INTO `classa` VALUES ('一班', '小红', '语文', 51);
INSERT INTO `classa` VALUES ('一班', '小红', '英语', 70);
INSERT INTO `classa` VALUES ('一班', '小蓝', '数学', 68);
INSERT INTO `classa` VALUES ('一班', '小蓝', '语文', 53);
INSERT INTO `classa` VALUES ('一班', '小蓝', '英语', 74);
INSERT INTO `classa` VALUES ('二班', '小明', '数学', 84);
INSERT INTO `classa` VALUES ('二班', '小明', '语文', 63);
INSERT INTO `classa` VALUES ('二班', '小明', '英语', 76);
INSERT INTO `classa` VALUES ('二班', '小楚', '数学', 84);
INSERT INTO `classa` VALUES ('二班', '小楚', '语文', 75);
INSERT INTO `classa` VALUES ('二班', '小楚', '英语', 25);
INSERT INTO `classa` VALUES ('一班', '王麻子', '数学', 92);
INSERT INTO `classa` VALUES ('一班', '王麻子', '语文', 52);
INSERT INTO `classa` VALUES ('一班', '王麻子', '英语', 76);
INSERT INTO `classa` VALUES ('一班', '赵四', '数学', 99);
INSERT INTO `classa` VALUES ('一班', '赵四', '语文', 58);
INSERT INTO `classa` VALUES ('一班', '赵四', '英语', 78);
INSERT INTO `classa` VALUES ('二班', '小红', '数学', 84);