表A,有姓名,分数,班级三个字段。我想查询每个班级分数第一的人叫什么名字,sql语句怎么写?
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
依然是窗口函数,
row_number() over(partition by 班级,order by 分数 desc) as rank
适用topk问题,只查一次原表
换个思路,分组未必是用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数据库使用
如果是在程序设计里面,可以分二条SQL语句,这样思路清晰,可读性强。
SELECT distinct classroom FROM students
上面的SQL可以查询到所有的班级
然后 SELECT MAX(score) FROM students WHERE classroom='班级'
这样分二条来写。
select name from students group by classroom having max(score)
SELECT name,classroom,MAX(score) FROM `student` GROUP BY classroom
我这里提供一种思路,自连接查询,我们一步步按过程去理解。如下:
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)
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
);
这两个好像都可以。
思路:分组排序,取每组第一个