SQL命令—查询综合成绩2到4名的学生学号

2021年3月19日 426点热度 1人点赞 0条评论

前两天面试的时候碰到了SQL查询命令,让查询综合成绩2到4名的学生学号,一下子蒙住了。

下来赶紧整理一下,发现mySQL好坑啊!!!

来一张可爱的猫咪图缓解一下郁闷的心情。

废话不多说。

首先给了一个很典型的学生成绩数据表:

成绩表:
score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

我们在数据库里面创建表并且写入数据:

-- 1 创建表格
create table if not exists score
(
 s_id VARCHAR(20) not null,
 c_id VARCHAR(20) not null,
 s_score VARCHAR(20) default null
)
engine=INNODB default charset=utf8;
-- 2 插入数据
insert into score(s_id,c_id,s_score) values('01','01','80');
insert into score(s_id,c_id,s_score) values('01','02','90');
insert into score(s_id,c_id,s_score) values('01','03','99');
insert into score(s_id,c_id,s_score) values('02','01','70');
insert into score(s_id,c_id,s_score) values('02','02','60');
insert into score(s_id,c_id,s_score) values('02','03','80');
insert into score(s_id,c_id,s_score) values('03','01','80');
insert into score(s_id,c_id,s_score) values('03','02','80');
insert into score(s_id,c_id,s_score) values('03','03','80');
insert into score(s_id,c_id,s_score) values('04','01','50');
insert into score(s_id,c_id,s_score) values('04','02','30');
insert into score(s_id,c_id,s_score) values('04','03','20');
insert into score(s_id,c_id,s_score) values('05','01','76');
insert into score(s_id,c_id,s_score) values('05','02','87');
insert into score(s_id,c_id,s_score) values('06','01','31');
insert into score(s_id,c_id,s_score) values('06','03','34');
insert into score(s_id,c_id,s_score) values('07','02','89');
insert into score(s_id,c_id,s_score) values('07','03','98');

然后我们看一下这个表的所有信息:

select *
from score;

然后我们考虑一下如何实现。

由于要找出排名2到4的学生,那么我们必须得出排名。我们以学号分组,然后用sun()函数求出每个学生总成绩,然后运用order by对其降序排列。此时,我们已经可以得到排完序后的学生信息,但是我们仍然需要排名。我们用@cur变量来指示排名或者用row_number()窗口函数来完成这一操作。最后,用where或者having语句限制到排名2到4的学生即可。

下面,分别给出5.7版本和8.0版本的mySQL示例代码:

5.7版本mySQL

由于5.7版本mySQL不支持窗口函数,只能用变量来指示排名。

而且,使用变量的方式两个版本的实现还是不一样的!!!

mysql> select s_id,scoreSum,scoreRank
    -> from
    -> (
    -> select b.s_id,b.scoreSum,@cur:=@cur+1 as scoreRank
    -> from
    -> (
    -> select s_id,sum(s_score) as scoreSum
    -> from score
    -> group by s_id
    -> order by sum(s_score) desc
    -> ) b,
    -> (select @cur:=0) c
    -> ) r
    -> where scoreRank between 2 and 4
    -> ;

8.0版本mySQL

首先就是用变量来指示排名的实现方式:

注意观察一下与5.7版本实现方式的区别!

mysql> set @cur=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select s_id,scoreSum,scoreRank
    -> from
    -> (
    -> select b.s_id,b.scoreSum,@cur:=@cur+1 as scoreRank
    -> from
    -> (
    -> select s_id,sum(s_score) as scoreSum
    -> from score
    -> group by s_id
    -> order by sum(s_score) desc
    -> ) b
    -> ) r
    -> where scoreRank between 2 and 4
    -> ;

第二种方法就是用8.0版本支持的row_number()窗口函数的实现方式:

mysql> select s_id,scoreSum,scoreRank
    -> from
    -> (
    -> select s_id,sum(s_score) as scoreSum,
    -> row_number() over(order by sum(s_score) desc) as scoreRank
    -> from score
    -> group by s_id
    -> ) b
    -> where scoreRank between 2 and 4
    -> ;

最终的返回结果如图所示:

agedcat_xuanzai

这个人很懒,什么都没留下

文章评论