2020.4.15练习

按各科成绩进行排序，并显示排名， Score 重复时合并名次

 select a.cid, a.sid, a.score, count(distinct b.score)+1 as rank
from sc  a 
left join sc  b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC

–查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc  a 
left join sc  b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC

这两个题的关键区别在于对于成绩相同的学生采取何种统计方式，合并名次即把相同成绩学生记为1，保留名次空缺则是统计有几人成绩就统计几人，不管他们是否相同，想清楚这点则不会被卡住

附两个类似题目：
–查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select t.sid,count( p.sid)+1   from 
(select s.sid,sum(s.score) sk1
from sc s
group by s.sid ) t left join 
(select s.sid,sum(s.score) sk2
from sc s
group by s.sid
) p  on  t.sk1 < p.sk2

group by t.sid
order by count(p.sid)+1 

–查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select t.sid,count( distinct p.sk2)+1   from 
(select s.sid,sum(s.score) sk1
from sc s
group by s.sid ) t left join 
(select s.sid,sum(s.score) sk2
from sc s
group by s.sid
) p  on  t.sk1 < p.sk2

group by t.sid
order by count(p.sid)+1