MySQl里类似Oracle rownum的实现

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433216

sql view plain copy

1. -- dense rank，写法1
2. set @curr_cut:=0, @prev_cnt:=0, @rank:=0;
3. select actor_id,
4. @curr\_cnt:=cnt **as** cnt,
5. @rank:=if(@prev\_cnt<>@curr\_cnt,@rank:=@rank+1,@rank) **as** rank
6. @prev\_cnt:=@curr\_cnt **as** dummy
7. from (select actor_id,count(*) as cnt from sakila.film_actor group by actor_id order by cnt desc limit 10) as der;

结果：

sql view plain copy

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

结果：

sql view plain copy

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

结果：

sql view plain copy

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

结果：

• 下一篇维度模型数据仓库（一） —— 概述