求助一条 SQL 语句的写法，分组查询，求每个学生第一次考试成绩和最后一次考试成绩的差值

学生 ID   考试时间         成绩 0        2020-01-01      60 0        2020-02-01      70 0        2020-03-01      80 1        2020-02-14      90 1        2020-03-15      80 

学生 ID     第一次成绩      最后一次成绩   差值 0          60             80            20 1          90             80            -10 

• 資深大佬 : kiracyan

  order by 考试时间

• 資深大佬 : loliordie

  Order by 考试时间 和 order by 考试时间 desc 组合起来即可

• 資深大佬 : beichenhpy

  mysql 的话我想是 按照时间 asc desc 然后 limit 1 ? 两个结果 union all 一下？。。Oracle 没用过。。

• 主 資深大佬 : LeeReamond

  @kiracyan
  @loliordie
  order by 之后呢，如何在分组中取出开头结尾？

• 資深大佬 : loading

  最好说一下是哪型数据库，有特殊语法。

• 資深大佬 : loading

  oracle 要用 row number 吧

• 資深大佬 : qiayue

  有一个特别简单的办法，用户表加两个字段，首次考试分数和最后一次考试分数，实际产品中，这是最佳解法

• 資深大佬 : yeqizhang

  主自己找到解法的话分享一下哈，我也学习学习

• 資深大佬 : l00t

  多年不写有些具体的忘记了，我就提几个关键字，主你自己去查一下补全吧。

  first_value(成绩)over(partition by ID order by 时间), last_value(成绩)over(partition by ID order by 时间)
• 主 資深大佬 : LeeReamond

  @l00t 感谢大佬，成功实现了。不过这个我做出来的结果是，比如源数据库共有两种学生 ID，那么我期望输出两行，但是他实际输出的行数与考试次数相等，然后每一行数据都一样，这种结果出来怎么处理？

  进行 group by 吗？感觉又浪费了一次算力。生产的最佳实践是什么？

• 資深大佬 : dzdh

  @LeeReamond 最佳实践看#7

• 資深大佬 : l00t

  @LeeReamond #10 加个 distinct

• 資深大佬 : isnullstring

  @qiayue 哈哈哈，没有比静态数据来更快的解法

• 資深大佬 : ebony0319

  select studentId,max(score),min(score),max(score)-min(score) from score s where 1=(
  select count(1) from score s1 where s.score>=s1.score and s.studentId=s1.studentId
  ) or 1=(
  select count(1) from score s1 where s.score<=s1.score and s.studentId=s1.studentId
  ) group by studentId

• 資深大佬 : ebony0319

  []( https://imgchr.com/i/rqRyNj)

• 資深大佬 : ebony0319

  抱歉,审错题了.我是按照最高分,最低分来写的.吧里面的条件改成 s.time>=s1.time 即可

• 資深大佬 : l00t

  @ebony0319 #16 条件可以改，但你聚合函数打算用什么？

• 資深大佬 : ebony0319

  @l00t 不变

• 資深大佬 : isir1234

  select student_id, gap
  from (select student_id,
  last_value(score) over (partition by student_id order by created_at) –
  first_value(score) over (partition by student_id order by created_at) AS gap,
  row_number() over (partition by student_id order by created_at desc) rn
  from exam) tmp
  where rn = 1;

• 資深大佬 : l00t

  @ebony0319 #18 不变不就错了？第一次考试又不是最高分，第二次又不是最低分。