MySQl里类似Oracle rownum的实现

发布时间：2024-01-11 04:34:58
[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;??

结果：

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

37

35

7

35


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

?结果：

actor_id

cnt

rank

107

42

1

102

41

2

198

40

3

181

39

4

23

37

5

81

36

6

158

35

7

13

35

7

37

35

7

144

35

7


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

结果：

actor_id

cnt

rank

107

42

1

102

41

2

198

40

3

181

39

4

23

37

5

81

36

6

158

35

7

13

35

8

37

35

9

144

35

10


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

结果：

actor_id

first_name

rownum

132

ADAM

1

71

ADAM

2

165

AL

3

173

ALAN

4

146

ALBERT

5
文章来源:https://wxy0327.blog.csdn.net/article/details/53908230
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章