MySQL面试题之分组排序并显示排名

题目

按各科成绩进行排序，并显示排名 !!!笛卡尔积链接

建表

-- 创建sc  s_id学生编号，c_id科目编号，score成绩
create table sc(
s_id varchar(10),
c_id varchar(10),
score int);

-- 导入数据
insert into sc values
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

解题思路

首先题目要求使用笛卡尔积连接，由于MySQL默认就是笛卡尔积连接，这一步就不用管了

由于使用的是MySQL5.7版本，是不能直接利用函数进行分组排序的（8.0版本是可以的）

1. 由于要求的是根据每个科目的成绩排序，也就是每个科目的成绩排名都会一个排名，所以需要每个科目排序后对设置的变量进行初始化
2. 先将数据根据科目升序,成绩降序排列,通过比较上下两条记录的科目是否相同，来确定排名是否需要初始化

答案及注释

select sc.*,
case when c_id = @last_cid then @rank:=@rank+1 else @rank:=1 end as rank,     #3、比较当前记录的科目与上一条记录的科目,如果相等排名加1,如果不等,说明下面记录是新的科目,此条记录在新科目中排名为1
@last_cid:=c_id                                      #4、在每条记录计算完名次之后,将当前记录的科目赋值给@last_cid,用于和下一条记录做比较
from sc, (select @last_cid:= '' , @rank:=0) t        #2、定义两个变量:@last_cid用于储存上一条记录的科目值,初始化为空字符串; @rank用于计算学生的名次,初始化为0
order by c_id ,score desc;                           #1、先将数据根据科目升序,成绩降序排列,然后只需要计算每条记录在同一科目中名次 rank 值即可