数据库
SQL
MySQL
SQL 语句
SQLAlchemy

表A,有姓名,分数,班级三个字段。我想查询每个班级分数第一的人叫什么名字,sql语句怎么写?

建表语句: CREATE TABLE `students` ( `id` int(11) unsigned NOT NULL AUTO_INCREME…
关注者
9
被浏览
7,032

9 个回答

这个问题有一种情况需要考虑到,就是如果一个班级里出现多名同学的分数并列第一,那么他们的名字都要查出来。

这个查询通过一个自然连接就可以做到,具体的 SQL 如下:

SELECT 
  NAME 
FROM
  students a 
  INNER JOIN 
    (SELECT 
      classroom,
      MAX(score) AS score 
    FROM
      students 
    GROUP BY classroom) b 
    ON b.classroom = a.classroom 
    AND b.score = a.score 

衍生表 b 是通过 students 表分组聚合出来的结果,它的数据是所有的班级及该班级里学生的最高分数。

窗口函数是在 MySQL 8.0 及以后的版本才出现,如果使用窗口函数,可以这么写:

SELECT 
  NAME 
FROM
  (SELECT 
    NAME,
    rank () over (
      PARTITION BY classroom 
  ORDER BY score DESC
  ) AS rk 
  FROM
    students) t 
WHERE rk = 1 

发布于 2020-05-17 00:15

依然是窗口函数,

row_number() over(partition by 班级,order by 分数 desc) as rank

适用topk问题,只查一次原表

发布于 2020-05-11 12:24

换个思路,分组未必是用group by实现的。两种最经典的求分组里面a列值最大的b列值(或者全列),not exists,开子窗口

select * from student t1
where not exists(
select * 
from t1 
where t1.classroom=t2.classroom
and t2.score>t1.score
)

隐患是没有处理同样分数最高的的人,但是从语法上来说同样最高分符合最高分的定义。

with aa as
( 
select *,
rank() over(group by classroom order by score desc) as rk
from student)
select * 
from aa 
where rk=1 

以上两个语句符合sql92,可以在大多数SLQ数据库使用

发布于 2020-06-06 10:50

如果是在程序设计里面,可以分二条SQL语句,这样思路清晰,可读性强。

SELECT distinct classroom FROM students

上面的SQL可以查询到所有的班级

然后 SELECT MAX(score) FROM students WHERE classroom='班级'

这样分二条来写。

发布于 2020-05-12 21:10

select name from students group by classroom having max(score)

发布于 2020-05-17 00:45

SELECT name,classroom,MAX(score) FROM `student` GROUP BY classroom

编辑于 2020-05-12 00:06

我这里提供一种思路,自连接查询,我们一步步按过程去理解。如下:

SELECT * FROM students a,students b;

这是一个关系代数的过程,查询出来的结果集是表自身的笛卡尔积。结果如下:

接下来,我们可以根据目标[每个班级和分数第一]来对查询结果进行筛选,每一条记录中,需要要求两点:班级相同分数更高。也即

查找同一班级,(b)比当前(a)记录分数更高的记录,如果只有一条,证明当前这条记录就是最高分了(不好理解,但请品、细细品...[手动狗头])

补充一下理解:比如当前(a)是陆离,比陆离高的有(b)陆离、陆建,那么陆离肯定不是最高的,但如果比陆离高的只有陆离自己,那么陆离肯定就是最高的。用>=也是为了把自己也计算在内。

这样我们可以添加两个查询条件了,如下:

SELECT * FROM students a,students b WHERE a.classroom=b.classroom AND b.score>=a.score;

得到的查询结果如下:

这个时候,我们按需求班级的最高分学生进行分组,如下:

SELECT * FROM students a,students b WHERE a.classroom=b.classroom AND b.score>=a.score
GROUP BY a.name, a.classroom;

得到的查询结果如下:

其实分组之前,我们就已经,像陆离、陆建都是有两条数据的,你可以增加COUNT(*)来查看分组记录条数,而我们的目标是比当前高的只有一条记录,那么就需要使用COUNT(*)=1来进行进一步筛选了,如下:

SELECT * FROM students a,students b WHERE a.classroom=b.classroom AND b.score>=a.score
GROUP BY a.name, a.classroom
HAVING COUNT(*)=1;

得到的结果如下:

这其实就是我们最终想要得到的结果了。

沃德天?同分重分怎么办?

这里也按以上思路走下来,一般情况下,同分不同排名是大多数榜单的排名。我们可以通过排序(分数高的,同时比当前记录分数高的个数)拿到一个基础榜单,同时去掉HAVING的查询条件,如下:

SELECT *,COUNT(*) AS ct FROM students a,students b 
WHERE a.classroom=b.classroom AND b.score>=a.score
GROUP BY a.name, a.classroom
ORDER BY a.score DESC, ct ASC;

得到结果(此处增加了SS和NN两位同学的分数同分)如下:

同分不同排名,那谁先排在前面?完全取决于具体业务需求,比如学号之类的,这里我们用id来进行业务性的排序,想必你应该已经知道要怎么做了,对了,就是加一个排序就可以了。

SELECT *,COUNT(*) AS ct FROM students a,students b 
WHERE a.classroom=b.classroom AND b.score>=a.score
GROUP BY a.name, a.classroom
ORDER BY a.score DESC, ct ASC, a.id DESC;

得到新的排行榜如下:

所以,你非得只要一个第一名怎么办?还记得我们一开始筛选最高分怎么做的吗?

查找同一班级,(b)比当前(a)记录分数更高的记录,如果只有一条,证明当前这条记录就是最高分了(不好理解,但请品、细细品...[手动狗头])

想到了吗?没错,就是这里,多加一个查询条件,这个条件就是上面我们用来排名的附加的业务需求排序

SELECT *,COUNT(*) AS ct FROM students a,students b 
WHERE a.classroom=b.classroom AND b.score>=a.score AND b.id>=a.id
GROUP BY a.name, a.classroom
HAVING ct=1;

得到的结果如下:

至此,便解决了同分的排名和获取唯一最高分的问题。

其他的取分和排行,也是可以延伸和扩展的。当然还有别的思路,比如子查询之类的,还有GROUP_CONCAT之类的思路也是可以的。

自连接、关系代数、笛卡尔积可以举一反三应用于不同的地方。

字字手打,如有所用,还望点赞、收藏、打个评语!谢谢!

我的主页有专栏,不定期更新相关技术等文章。
也欢迎关注我的私人公众号:麦纳斯罗大陆(landminerslo)
编辑于 2020-05-09 19:49

SELECT

a.score,

a.`name`

FROM

students AS a

JOIN (

SELECT

max( score ) as mscore

FROM

students as b

GROUP BY

classroom

) AS b ON a.score = b.mscore;


SELECT

score,

`name`

FROM

students

WHERE

score IN (

SELECT

max( score )

FROM

students

GROUP BY

classroom

);

这两个好像都可以。

编辑于 2020-05-18 10:02

思路:分组排序,取每组第一个

发布于 2020-05-10 10:11