前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQl里类似Oracle rownum的实现

MySQl里类似Oracle rownum的实现

作者头像
用户1148526
发布2019-05-25 19:46:02
2K0
发布2019-05-25 19:46:02
举报
文章被收录于专栏:Hadoop数据仓库

版权声明:本文为博主原创文章,未经博主允许不得转载。 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;

结果:

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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2016年12月28日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档