row_number()

select @rownum:=@rownum+ as rn,t.* from (select @rownum:=) r, test_table t

select if(@uid=t.user_id and @cid=t.city_id,@rank:=@rank+,@rank:=) as rank,t.*,@uid:=t.user_id,@cid:=t.city_id

from (select @uid:=null,@cid:=null,@rank:=) r, (select user_id,city_id,city_name,id from t_user_city order by user_id,city_id,id) t;

set @rank:=;

set @CI:=null;

select SId,CId,score,rank from(

select

SId, CId,score,

@rank:=if(@CI=CId,@rank+,) as rank,

@CI:=CId

from data.sc

order by CId,score desc) as t1