MySQL窗口函数学习 排名，分组内排名等

开窗函数汇总

图片来自知乎：https://zhuanlan.zhihu.com/p/165210822

环境准备：Mysql 8

表结构：

---

DROP TABLE IF EXISTS stu;
CREATE TABLE stu (
n_id int(11) NOT NULL AUTO_INCREMENT,
c_name varchar(5) DEFAULT NULL,
n_age int(11) DEFAULT NULL,
c_class varchar(2) DEFAULT NULL,
n_socre int(4) DEFAULT NULL,
PRIMARY KEY (n_id),
KEY idx_id_name (n_id,c_name) USING BTREE,
KEY idx_class (c_name,c_class) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

---

-- Records of stu

---

INSERT INTO stu VALUES ('1', '张三', '12', '1', '55');
INSERT INTO stu VALUES ('2', '李四', '11', '1', '66');
INSERT INTO stu VALUES ('3', '王二', '13', '1', '66');
INSERT INTO stu VALUES ('4', 'AA', '7', '2', '98');
INSERT INTO stu VALUES ('5', 'BB', '5', '2', '98');
INSERT INTO stu VALUES ('6', 'CC', '6', '2', '44');
INSERT INTO stu VALUES ('7', '爱因斯坦', '7', '3', '100');
INSERT INTO stu VALUES ('8', '马小六', '11', '1', '55');

按分数排名输出 rank（）

注意排名重复的话会占用下一个的名次

select n_id, c_name,n_socre ,rank() over (order by n_socre) from stu;

按分数排名输出 dense_rank（）

select n_id, c_name,n_socre ,dense_rank() over (order by n_socre) from stu

注意排名重复的话不会占用下一个的名次

按分数排名输出序号 row_number（）

select n_id, c_name,n_socre ,row_number() over (order by n_socre) from stu

注意排名不会重复，分数相同序号会递增

同时输出班级总人数、班级总分 使用partition by

select n_id, c_name,n_socre ,c_class, count(n_id) over (partition by c_class) 当前班级总人数, sum(n_socre) over(partition by c_class) 班级总分 from stu

同时输出班级总人数、班级总分 使用 order by

注意 里面用order by 输出的聚合结果时递增的，每次会涉及到之前分组的数据

select n_id, c_name,n_socre ,c_class, count(n_id) over (order by c_class) 当前班级总人数, sum(n_socre) over(order by c_class)  班级总分 from stu

每行展示当前同学 所在班级内排名，全校排名，班级内最大分数，全校最大分数值

select n_id, c_name,n_socre ,c_class, dense_rank() over (partition by c_class order by n_socre) 当前同学在班级内排名, max(n_socre) over(partition by c_class)  
班级内最大分数,max(n_socre) over() 全校最大分数 from stu

每行输出 最后一名、第一名、前一名、后一名，当前成绩，当前排名

SELECT
	n_id,
	c_name,
	n_socre,
	rank () over (ORDER BY n_socre) 分数排名,
	lead (n_socre, 1) over () 后一名成绩,
	lag (n_socre, 1) over () 前一名成绩， 
        first_value (n_socre) over () 第一名成绩，
        last_value (n_socre) over () 最后一名成绩
FROM
	stu