mysql实现row_number()和row_number() over(partition by)

row_number()

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

解释：给test_table里的数据设置行号, rn是行号

row_number() over(partition by)

select if(@uid=t.user_id and @cid=t.city_id,@rank:=@rank+1,@rank:=1) as rank,t.*,@uid:=t.user_id,@cid:=t.city_id 
from (select @uid:=null,@cid:=null,@rank:=0) r, (select user_id,city_id,city_name,id from t_user_city order by user_id,city_id,id) t;

解释：对t_user_city表按照user_id,city_id两个字段做分组设置行号,rank是分组的行号,order by里一定要有两个分组字段及第三个排序字段