每年每门学科排名第一的学生 和每年总成绩都有所提升的学生

一张学生成绩表(student_scores)，有year-学年，subject-课程，student-学生，score-分数这四个字段，请完成如下问题：

问题1：每年每门学科排名第一的学生

问题2：每年总成绩都有所提升的学生

CREATE TABLE t1_student_scores
(
year varchar(15),
subject varchar(15),
student varchar(15),
score INT );
-- 数据插入语句
INSERT INTO t1_student_scores
(year, subject, student, score) VALUES
(2018, '语文', 'A', 84),
(2018, '数学', 'A', 59),
(2018, '英语', 'A', 30),
(2018, '语文', 'B', 44),
(2018, '数学', 'B', 76),
(2018, '英语', 'B', 68),
(2019, '语文', 'A', 51),
(2019, '数学', 'A', 94),
(2019, '英语', 'A', 71),
(2019, '语文', 'B', 87),
(2019, '数学', 'B', 44),
(2019, '英语', 'B', 38),
(2020, '语文', 'A', 91),
(2020, '数学', 'A', 50),
(2020, '英语', 'A', 89),
(2020, '语文', 'B', 81),
(2020, '数学', 'B', 84),
(2020, '英语', 'B', 98);

问题1:

FIRST_VALUE 是MySQL中的一个窗口函数，它返回与当前行相关的窗口框架的第一行的值

使用窗口函数:

select distinct year ,subject,first_student from(
select year,subject,student,score,
first_value(student) over (partition by year,subject order by score desc) as first_student
from t1_student_scores) temp;

使用连接查询:

select temp.year,temp.subject,student
from t1_student_scores ,
(select year,subject,Max(score) score
from t1_student_scores
group by year,subject) temp
where temp.year=t1_student_scores.year and temp.subject=t1_student_scores.subject and temp.score=t1_student_scores.score
order by year,subject

问题2：每年总成绩都有所提升的学生

1.计算每年每个学生的总成绩

select year, student, sum(score) as total_score
from t1_student_scores
group by year, student

 

2.使用lag函数，在本行添加上一学年成绩

LAG 是MySQL中的一个窗口函数，它允许你访问结果集中当前行之前的行中的数据

LAG函数通常有三个参数：列名、偏移量和默认值。

偏移量表示我们想要向上查看多少行,默认值为1

默认值则是当没有足够的行可供偏移时返回的值。如果不指定默认值，当偏移量超出范围时，LAG函数将返回NULL。

select year,
       student,
       total_score,
       lag(total_score) over (partition by student order by year) as last_year_score
from (select year, student, sum(score) as total_score
      from t1_student_scores
      group by year, student) t

3.剔除lag()结果字段为空数据，然后比较判断是否有进步

select year,
       student,
       total_score,
       last_year_score,
       if(total_score > last_year_score, 1, 0) as improve_flag
from (select year,
             student,
             total_score,
             lag(total_score) over (partition by student order by year) as last_year_score
      from (select year, student, sum(score) as total_score
            from t1_student_scores
            group by year, student) t) t1
where last_year_score is not null

4.取每年进步

根据student分租,如果行数=sum(improve_flag)表示每年都进步

UNT函数可以接受一个参数，这个参数可以是*（表示计数所有行，包括NULL值所在的行），或者是某个列的名称（表示只计数那些列值不为NULL的行）。

COUNT(1)会计算结果集中的非NULL值的数量，因为1永远不是NULL。这与COUNT(*)的行为类似，后者也是计数所有行，不管列值是否为NULL。

select student
from(
select year,
       student,
       total_score,
       last_year_score,
       if(total_score > last_year_score, 1, 0) as improve_flag
from (select year,
             student,
             total_score,
             lag(total_score) over (partition by student order by year) as last_year_score
      from (select year, student, sum(score) as total_score
            from t1_student_scores
            group by year, student) t) t1
where last_year_score is not null) temp
group by student
having count(1)=sum(improve_flag)