香港云主机最佳企业级服务商!

ADSL拨号VPS包含了中国大陆(联通,移动,电信,)

中国香港,国外拨号VPS。

当前位置:云主机 > MYSQL >

电信ADSL拨号VPS
联通ADSL拨号VPS
移动ADSL拨号VPS

总结几种MySQL中常见的排名问题


时间:2020-11-03 13:48 作者:admin610456


前言:

在某些应用场景中,我们经常会遇到一些排名的问题,比如按成绩或年龄排名。排名也有多种排名方式,如直接排名、分组排名,排名有间隔或排名无间隔等等,这篇文章将总结几种mysql/' target='_blank'>mysql中常见的排名问题。

创建测试表

create table scores_tb ( id int auto_increment primary key, xuehao int not null,  score int not null) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into scores_tb (xuehao,score) values (1001,89),(1002,99),(1003,96),(1004,96),(1005,92),(1006,90),(1007,90),(1008,94);# 查看下插入的数据MySQL> select * from scores_tb;+----+--------+-------+| id | xuehao | score |+----+--------+-------+| 1 | 1001 | 89 || 2 | 1002 | 99 || 3 | 1003 | 96 || 4 | 1004 | 96 || 5 | 1005 | 92 || 6 | 1006 | 90 || 7 | 1007 | 90 || 8 | 1008 | 94 |+----+--------+-------+

1.普通排名

按分数高低直接排名,从1开始,往下排,类似于row number。下面我们给出查询语句及排名结果。

# 查询语句SELECT xuehao, score, @curRank := @curRank + 1 AS rankFROM scores_tb, (SELECT @curRank := 0) rORDER BY score desc;# 排序结果+--------+-------+------+| xuehao | score | rank |+--------+-------+------+| 1002 | 99 | 1 || 1003 | 96 | 2 || 1004 | 96 | 3 || 1008 | 94 | 4 || 1005 | 92 | 5 || 1006 | 90 | 6 || 1007 | 90 | 7 || 1001 | 89 | 8 |+--------+-------+------+

上述查询语句中,我们申明了一个变量 @curRank ,并将此变量初始化为0,查得一行将此变量加一,并以此作为排名。我们看到这类排名是没间隔的并且有些分数相同但排名不同。

2.分数相同,名次相同,排名无间隔

# 查询语句SELECT xuehao, score, CASEWHEN @prevRank = score THEN @curRankWHEN @prevRank := score THEN @curRank := @curRank + 1END AS rankFROM scores_tb, (SELECT @curRank :=0, @prevRank := NULL) rORDER BY score desc;# 排名结果+--------+-------+------+| xuehao | score | rank |+--------+-------+------+| 1002 | 99 | 1 || 1003 | 96 | 2 || 1004 | 96 | 2 || 1008 | 94 | 3 || 1005 | 92 | 4 || 1006 | 90 | 5 || 1007 | 90 | 5 || 1001 | 89 | 6 |+--------+-------+------+

3.并列排名,排名有间隔

另外一种排名方式是相同的值排名相同,相同值的下一个名次应该是跳跃整数值,即排名有间隔。

# 查询语句SELECT xuehao, score, rank FROM(SELECT xuehao, score,@curRank := IF(@prevRank = score, @curRank, @incRank) AS rank, @incRank := @incRank + 1, @prevRank := scoreFROM scores_tb, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) rORDER BY score desc) s;# 排名结果+--------+-------+------+| xuehao | score | rank |+--------+-------+------+| 1002 | 99 | 1 || 1003 | 96 | 2 || 1004 | 96 | 2 || 1008 | 94 | 4 || 1005 | 92 | 5 || 1006 | 90 | 6 || 1007 | 90 | 6 || 1001 | 89 | 8 |+--------+-------+------+

上面介绍了三种排名方式,实现起来还是比较复杂的。好在MySQL8.0增加了窗口函数,使用内置函数可以轻松实现上述排名。

MySQL8.0 利用窗口函数实现排名

MySQL8.0中可以利用 ROW_NUMBER(),DENSE_RANK(),RANK() 三个窗口函数实现上述三种排名,需要注意的一点是as后的别名,千万不要与前面的函数名重名,否则会报错,下面给出这三种函数实现排名的案例:

# 三条语句对于上面三种排名select xuehao,score, ROW_NUMBER() OVER(order by score desc) as row_r from scores_tb;select xuehao,score, DENSE_RANK() OVER(order by score desc) as dense_r from scores_tb;select xuehao,score, RANK() over(order by score desc) as r from scores_tb;# 一条语句也可以查询出不同排名SELECT xuehao,score, ROW_NUMBER() OVER w AS 'row_r', DENSE_RANK() OVER w AS 'dense_r', RANK()  OVER w AS 'r'FROM `scores_tb`WINDOW w AS (ORDER BY `score` desc);# 排名结果+--------+-------+-------+---------+---+| xuehao | score | row_r | dense_r | r |+--------+-------+-------+---------+---+| 1002 | 99 |  1 |  1 | 1 || 1003 | 96 |  2 |  2 | 2 || 1004 | 96 |  3 |  2 | 2 || 1008 | 94 |  4 |  3 | 4 || 1005 | 92 |  5 |  4 | 5 || 1006 | 90 |  6 |  5 | 6 || 1007 | 90 |  7 |  5 | 6 || 1001 | 89 |  8 |  6 | 8 |+--------+-------+-------+---------+---+

总结:

本文给出三种不同场景下实现统计排名的SQL,可以根据不同业务需求选取合适的排名方案。对比MySQL8.0,发现利用窗口函数可以更轻松实现排名,其实业务需求远远比我们举的示例要复杂许多,用SQL实现此类业务需求还是需要慢慢积累的。

以上就是总结几种MySQL中常见的排名问题的详细内容,更多关于MySQL 排名的资料请关注脚本之家其它相关文章!

(责任编辑:admin)






帮助中心
会员注册
找回密码
新闻中心
快捷通道
域名登录面板
虚机登录面板
云主机登录面板
关于我们
关于我们
联系我们
联系方式

售前咨询:17830004266(重庆移动)

企业QQ:383546523

《中华人民共和国工业和信息化部》 编号:ICP备00012341号

Copyright © 2002 -2018 香港云主机 版权所有
声明:香港云主机品牌标志、品牌吉祥物均已注册商标,版权所有,窃用必究

云官方微信

在线客服

  • 企业QQ: 点击这里给我发消息
  • 技术支持:383546523

  • 公司总台电话:17830004266(重庆移动)
  • 售前咨询热线:17830004266(重庆移动)