[MySQL] SQL 分组统计 分区排名

1、sql语句-建表、插入数据的语句

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
  `id`  int(6),
  `name` varchar(255),
  `sex` varchar(255),
  `subject` varchar(30),
  `score` float
) ENGINE = InnoDB;

INSERT INTO `student_score` VALUES (1, '小明', '男','语文', 80);
INSERT INTO `student_score` VALUES (2, '小红', '女','语文', 70);
INSERT INTO `student_score` VALUES (3, '小哈', '女','语文', 88);
INSERT INTO `student_score` VALUES (1, '小明', '男','数学', 66);
INSERT INTO `student_score` VALUES (2, '小红', '女','数学', 70);
INSERT INTO `student_score` VALUES (3, '小哈', '女','数学', 89);
INSERT INTO `student_score` VALUES (1, '小明', '男','英语', 80);
INSERT INTO `student_score` VALUES (2, '小红', '女','英语', 70);
INSERT INTO `student_score` VALUES (3, '小哈', '女','英语', 68);

2、查询所有学生的成绩：

    select * from student_score;

3.1 处理重复的列-方式1-合并去除重复的列值[列转行]

对应到常见的sql应用场景，统计各个学生的各科成绩，实现方式有两种，一种是分组统计的方式，一种是分区排名的方式
分组统计:

select id,name,sex,
	max(case when subject='语文' then score else 0 end) as chinese,
	max(case when subject='英语' then score else 0 end) as english,
	max(case when subject='数学' then score else 0 end) as math
from student_score 
group by id
order by score desc

结果：
按成绩降序排序，可以看到默认选择第一门学科-语文的成绩进行降序排序。

3.2 处理重复的列-方式2-对重复的列值进行排名

分区排名

select id,name,subject,score,
       row_number() over(partition by subject order by score desc) rn
from student_score;
# MySQL 8.0以上

分组统计：GROUP BY 结合 统计/聚合函数一起使用

-- 举例子: 按照性别统计男生、女生的人数

select sex,count(distinct id) sex_num from student_score group by sex;

分区排名：ROW_NUMBER() OVER(PARTITION BY 分区的字段 ORDER BY 升序/降序字段 [DESC])

-- 举例子: 按照性别-男生、女生进行分区，按照成绩进行降序

select id,name,sex,score,
ROW_NUMBER() OVER(PARTITION BY sex ORDER BY score DESC) rn
from student_score;

Reference

https://www.cnblogs.com/shan333/p/17206824.html