MySql8开窗函数

测试数据表：

CREATE TABLE `school_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(1) DEFAULT NULL,
  `course` char(10) DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (1, 'A', 'Chinese', 80);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (2, 'B', 'Chinese', 90);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (3, 'C', 'Chinese', 70);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (4, 'A', 'Math', 70);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (5, 'B', 'Math', 100);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (6, 'C', 'Math', 80);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (7, 'A', 'English', 90);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (8, 'B', 'English', 85);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (9, 'C', 'English', 99);

 

开窗函数排名 row_number () over (partition by ) rank 作为关键字 不能用于别名

SELECT name,course,score score,
row_number( ) over (PARTITION by course order by score desc)  as score_rank
from school_score;

查询各科成绩第一的人 或者前两名

SELECT * from (
select name,course,score ,row_number() over (PARTITION by course order by score) as score_rank from school_score
) as a  where a.score_rank in (1,2);

不公平排序 会跳过重复的序号

select name,course,rank() over (order by score) as score_rank from school_score;

公平排序 序号连续 但是会并列序号 11 11 

select name,course,score,dense_rank() over (order by score) as score_rank from school_score;

row_number 排序 不会出现并列 1 2 3

select name,course,score, row_number() over (order by score) as score_rank from school_score;

 

分桶，为结果集按行数分成不同的桶，比如 ntile(2) 就对结果集分分两组 nitil(3) 分三组，以此类推，如果分的组或者桶数不均，第一组或第一桶则数量加1

不均等

select name,course,score,  ntile(4) over(order by score desc) as score_rank from school_score;

均等

select name,course,score,  ntile(3) over(order by score desc) as score_rank from school_score;