MySQL 中 over（）函数的简单使用 dense_rank（），rank（） ，row_number（）

dence_rank在并列关系是，相关等级不会跳过。rank则跳过

rank() 是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank() 是连续排序，有两个第二名时仍然跟着第三名。
ROW_NUMBER() 就是直接按顺序，两个相同的值也是分为二三名排序。

班级分数表

Id	Score	Class
1	3.5	班级1
2	3.65	班级1
3	4	班级1
4	3.85	班级2
5	4	班级2
6	3.65	班级2
7	4	班级2
8	4	班级2
9	6.7	班级2
10	4.5	班级2
11	4.4	班级1
12	9.4	班级1
13	7.4	班级1

CREATE TABLE `score`  (
  `Id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `Score` decimal(20, 10) NOT NULL COMMENT '分数',
  `Class` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NULL DEFAULT NULL COMMENT '班级',
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_unicode_ci COMMENT = '分数表' ROW_FORMAT = Dynamic;

INSERT INTO `score` VALUES (1, 3.5000000000, '班级1');
INSERT INTO `score` VALUES (2, 3.6500000000, '班级1');
INSERT INTO `score` VALUES (3, 4.0000000000, '班级1');
INSERT INTO `score` VALUES (4, 3.8500000000, '班级2');
INSERT INTO `score` VALUES (5, 4.0000000000, '班级2');
INSERT INTO `score` VALUES (6, 3.6500000000, '班级2');
INSERT INTO `score` VALUES (7, 4.0000000000, '班级2');
INSERT INTO `score` VALUES (8, 4.0000000000, '班级2');
INSERT INTO `score` VALUES (9, 6.7000000000, '班级2');
INSERT INTO `score` VALUES (10, 4.5000000000, '班级2');
INSERT INTO `score` VALUES (11, 4.4000000000, '班级1');
INSERT INTO `score` VALUES (12, 9.4000000000, '班级1');
INSERT INTO `score` VALUES (13, 7.4000000000, '班级1');

dense_rank()

整个年级按分数排序

SELECT
    class,
    score,
    dense_rank ( ) over ( ORDER BY score DESC) order_row 
FROM
    `score`;

每个班级按分数排序

SELECT
    class,
    score,
    dense_rank ( ) over ( partition by Class ORDER BY score DESC) order_row 
FROM
    `score`;

每个班级按分数排序取成绩第2名

SELECT
    class, score,
FROM
    ( SELECT class, score, dense_rank ( ) over ( PARTITION BY Class ORDER BY score DESC ) order_row FROM `score` ) order_score 
WHERE
    order_row =2