sql 计算各科成绩排名

查询课程01,02,03的成绩排名，先显示01课程再02课程，再03课程

---成绩相同时，并列，比如：并列第一看图片前两项数据
select a.`SId` as '学号' ,
       a.`CId` as '课程id',
       a.score as '成绩',
       (select count(distinct b.score) from `SC` b where a.score<b.score and b.`CId`=a.`CId` )+1 as rank 
   from `SC` a  
   order by a.`CId`, rank

2.成绩相同时，学号大的在后面

select a.`SId` as '学号' ,
       a.`CId` as '课程id',
       a.score as '成绩',
       (select count(b.score)  from `SC` b  where  (a.score<b.score or (a.score=b.score and a.`SId`>b.`SId`) )  and  b.`CId`=a.`CId` and a.`SId`!=b.`SId` )+1 as rank    
from `SC` a 
order by a.`CId`, rank