set @number := 0,@last_order_id := 0;
select sc.SId, score, @number := case
when @last_order_id = sc.CId then @number + 1
else 1
end as num,
@last_order_id := CId as cid
from sc
order by sc.CId,sc.score desc ;
其中last_cid相当于上一次的排序的字段值。有个小问题,如果我初始化的last_cid一样时,就会出错了。必须俩层字段排序,因为第一层只是为了分组,第二次才是对对应的字段排序