MySQL8中的11个窗口函数

MySQL8.0之前，做数据排名统计等相当痛苦，因为没有像Oracle、SQL Server、PostgreSQL等其他数据库那样的窗口函数。但随着MySQL8.0中新增了窗口函数之后，针对这类统计就再也不是事了，本文就以常用的排序实例介绍MySQL的窗口函数。

1、准备工作

准备数据

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '里斯');
INSERT INTO `teacher` VALUES ('03', '王武');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '瑞兹', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '冰冰', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '赵丽', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '提莫', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '罗拉', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '希维尔', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '克拉拉', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '锐雯', '1990-01-20', '女');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_score` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

2、简单实用窗口函数对学生成绩进行统计分析

2.1、求每门课程的成绩排名（由高到低）

SELECT 
  *,
  rank () over (
    PARTITION BY c_id 
ORDER BY s_score DESC
) 名次 
FROM
  score ;

2.2、查询每位学生的成绩总分并排名

SELECT 
  s_id,
  SUM(s_score) 总成绩,
  rank () over (
ORDER BY SUM(s_score) DESC) 排名 
FROM
  score 
GROUP BY s_id ;

2.3 、查询每门功课成绩最好的前两名学生姓名及功课

SELECT 
  a.s_id,
  a.s_name,
  b.c_id,
  b.rankLevel 
FROM
  student a 
  JOIN 
    (SELECT 
      s_id,
      c_id,
      row_number () over (
        PARTITION BY c_id 
    ORDER BY s_score DESC
    ) rankLevel 
    FROM
      score) b 
    ON a.s_id = b.s_id 
WHERE b.rankLevel <= 2 ;

特别说明：MySQL8之后，rank等也作为关键字存在，数据库字段命名时不要使用关键字，以免执行SQL时报错。

3、窗口函数总结