mysql类似oracle的rownum_MySQl里类似Oracle rownum的实现 | 学步园

-- 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;

结果：

actor_id

cnt

rank

dummy

107

42

1

42

102

41

2

41

198

40

3

40

181

39

4

39

23

37

5

37

81

36

6

36

106

35

7

35

158

35

7

35

13

35

7

35