MySQL实现窗口函数row_number():选取班级里每科最高成绩的同学
程序员文章站
2022-07-13 17:14:15
...
有这样一组数据:
Name | Subject | Score |
---|---|---|
小明 | 语文 | 66 |
小明 | 数学 | 67 |
小明 | 英语 |
69 |
小红 | 语文 | 65 |
小红 | 数学 | 99 |
小红 | 英语 | 88 |
小强 | 语文 | 60 |
小强 | 数学 | 72 |
小强 | 英语 | 92 |
我们需要选取出每个科目最高的成绩以及对应的同学和科目
大数据如hivesql里可以使用很简便的rownum这个窗口函数来使用
SELECT
t1.name,
t1.subject,
t1.score
FROM
(
SELECT
t.name,
t.subject,
t.score,
row_number() OVER (partition BY t.name ORDER BY t.score DESC) AS rn
FROM
T t
) t1
WHERE
t1.rn = 1
但是大部分RDBMS数据库并无这个函数,这个时候我们就需要使用更为一般的方法(子查询)来实现
首先进行分析,单纯的进行max并不行,我们需要group by subject,但又需要group by name,这样两个分组就会造成数据的不唯一性,也就是会产生三个同学的成绩都会出现,不是我们所期望达到的。
那么我们想要的是什么呢,比如语文里最高分是66,对应的是小明。所以如果能按科目分组限定出每个科目的最高分后,能把这个条件附加到选项里挑选出对应的name即可。这时候灵感就会爆发出来了,没错就是join来内关联下
只需要我们按科目subject来分组选出最大的分数score,然后再把这个subject和score的值在on 里去限定原表,那么原表选出来的所有结果就是我们想要的结果
代码如下
SELECT
b.name,
b.subject,
b.score
FROM
(
SELECT
subject,
MAX (score) score
FROM
T
GROUP BY
subject
) a
JOIN T b ON a.subject = b.subject
AND a.score = b.score
最终选出结果为: