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

This topic created in 2141 days ago, the information mentioned may be changed or developed.

目前有一表如下，共三列

学生 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

也就是想要通过 group by 根据学生 ID 进行分组，之后取出某组中的初次和末次。平台 Oracle，看了一下 Oracle 分组查询的教学，似乎 Oracle 分组里面只有 max，min，avg 之类的函数没有办法按顺序取某条某条吗？有没有大佬讲解一下，谢谢

Oracle

分组

差值

成绩

20 replies • 2020-12-30 17:47:00 +08:00

kiracyan

Dec 29, 2020

order by 考试时间

loliordie

Dec 29, 2020 via Android

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

zm8m93Q1e5otOC69

Dec 29, 2020

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

LeeReamond

Dec 29, 2020

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

loading

Dec 29, 2020 via Android

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

loading

Dec 29, 2020 via Android

oracle 要用 row number 吧

qiayue

Dec 29, 2020

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

yungo8

Dec 29, 2020 via Android

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

l00t

Dec 29, 2020

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

first_value(成绩)over(partition by ID order by 时间), last_value(成绩)over(partition by ID order by 时间)

LeeReamond

Dec 30, 2020

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

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

dzdh

Dec 30, 2020

@LeeReamond 最佳实践看#7

l00t

Dec 30, 2020

@LeeReamond #10 加个 distinct

isnullstring

Dec 30, 2020

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

ebony0319

Dec 30, 2020

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

Dec 30, 2020

[![rqRyNj.png]( https://s3.ax1x.com/2020/12/30/rqRyNj.png)]( https://imgchr.com/i/rqRyNj)

ebony0319

Dec 30, 2020

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

l00t

Dec 30, 2020

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

ebony0319

Dec 30, 2020

@l00t 不变

isir1234

Dec 30, 2020

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

Dec 30, 2020

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