目前8.0及以上版本是支持row_number( ) over( )的,至于5.X,可以通过变量的方式实现。
简单测试如下:求每个人创建角色,按创建时间倒序排列
select create_person,
if(@create_person = a.create_person, @curRank := @curRank + 1, @curRank := 1) AS row_cnt,
@create_person := a.create_person,
create_time
from (select distinct create_person, create_time
from sys_role
where create_person in ('甲','乙')
order by create_person, create_time desc) a,
(select @create_person := null, @curRank := 0) temp;