SQL-按各科成绩进行排序，并显示排名(清晰解法)

按各科成绩进行排序，并显示排名（定义变量set @rank）

CREATE TABLE Score (
s_id VARCHAR(20), #学号
c_id VARCHAR(20), #课程编号
s_score INT(3),   #成绩
PRIMARY KEY(s_id,c_id)
);

解：

#1.排列各科成绩

select * from Score where c_id = '01' order by s_score desc;
select * from Score where c_id = '02' order by s_score desc;
select * from Score where c_id = '03' order by s_score desc;

#2.定义rank排名

set @rank_01 = 0;
select *,@rank_01 := @rank_01 + 1 from Score sc where c_id = '01'
order by s_score desc;
set @rank_02 = 0;
select *,@rank_02 := @rank_02 + 1 from Score sc where c_id = '02'
order by s_score desc;
set @rank_03 = 0;
select *,@rank_03 := @rank_03 + 1 from Score sc where c_id = '03'
order by s_score desc;

#3.合并查询结果

set @rank_01 = 0,@rank_02 = 0,@rank_03 = 0;
select * from
(select s_id as s_id01,c_id as c_id01,s_score as s_score01,@rank_01 := @rank_01 + 1 as rank01 from Score sc where c_id = '01' order by s_score desc) as a
join 
(select s_id as s_id02,c_id as c_id02,s_score as s_score02,@rank_02 := @rank_02 + 1 as rank02 from Score sc where c_id = '02' order by s_score desc) as b on a.rank01 = b.rank02
join
(select s_id as s_id03,c_id as c_id03,s_score as s_score03,@rank_03 := @rank_03 + 1 as rank03 from Score sc where c_id = '03' order by s_score desc) as c on b.rank02 = c.rank03

#4.查询结果