每年每门学科排名第一的学生

每年每门学科排名第一的学生

2020,语文,A,83
2020,数学,A,58
2020,英语,A,29
2020,语文,B,83
2020,数学,B,77
2020,英语,B,67
2021,语文,A,50
2021,数学,A,93
2021,英语,A,70
2021,语文,B,86
2021,数学,B,43
2021,英语,B,39
2022,语文,A,90
2022,数学,A,49
2022,英语,A,80
2022,语文,B,80
2022,数学,B,83
2022,英语,B,97

思路:

提取关键词每年、每门学科、排名第一

解决:

按年份和学科分组，求分数的最高值

实现方法1: 使用max()函数拿到分组后的最大分数，自己join自己，拿到最大分数对应的学生，好处是实现的结果如果有多个第一会同时拿到。

select
	b.year,b.course,b.student
from
	(select
		year,course,max(score) as max_score
	from
		scores
	group by
		year,course) a
join scores b
on a.year = b.year and a.course=b.course and a.max_score = b.score

实现方法2: 使用窗口的max()函数开窗拿到最高分数，做个过滤拿到和最高分数相同分数的学生，好处是实现的结果如果有多个第一会同时拿到。

select
	year,course,student
from(
	select
		year,course,student,score,
		max(score) over(partition by year,course order by year) max_score
	from
		scores
) a 
where a.score = a.max_score

实现方法3: 使用first_value()函数开窗，对年份、课程分区，对分数降序排序，拿到第一条记录，缺点是结果重复需要去重，实现的结果如果有多个第一只会拿一条结果。

select
	distinct year,course,
	first_value(student) over(partition by year,course order by score desc) student
from 
	scores
order by 
	year

该需求实现方式多样。