学步园

-- dense rank，写法1
set @curr_cut:=0, @prev_cnt:=0, @rank:=0;
select actor_id, 
       @curr_cnt:=cnt as cnt,
       @rank:=if(@prev_cnt<>@curr_cnt,@rank:=@rank+1,@rank) as rank
       @prev_cnt:=@curr_cnt as dummy
from (select actor_id,count(*) as cnt from sakila.film_actor group by actor_id order by cnt desc limit 10) as der;

结果：

-- dense rank，写法2，不使用dummy列
set @curr_cnt:=0, @prev_cnt:=0, @rank:=0;
select actor_id,
       @curr_cnt:=cnt as cnt,
       @rank:=if(@prev_cnt<>@curr_cnt,@rank:=@rank+1,@rank) as rank
from (select actor_id,count(*) as cnt from sakila.film_actor group by actor_id order by cnt desc limit 10) as der
where least(0,@prev_cnt:=@curr_cnt)=0;

结果：

-- rank
set @curr_cnt:=0, @rank:=0;
select actor_id,@curr_cnt:=cnt as cnt,@rank:=@rank+1 as rank
from (select actor_id,count(*) as cnt from sakila.film_actor group by actor_id oeder by cnt desc limit 10) as der;

结果：

-- rownum
set @rownum:=0;
select actor_id,first_name,@rownum:=@rownum+1 as rownum from sakila.actor order by first_name limit 5;

结果：