MySQl里类似Oracle rownum的实现
发布时间:2024-01-11 04:34:58
- ??
- 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 |
37 | 35 | 7 | 35 |
- ??
- 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;??
?结果:
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 |
- ??
- 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;??
结果:
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 |
- ??
- set?@rownum:=0;??
- 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
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:chenni525@qq.com进行投诉反馈,一经查实,立即删除!