欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

最终选出结果为:

相关标签: sql row_number