新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT > 数据库 > MYSQL

在MySQL中实现Rank高级排名函数

时间:2019-05-14 09:28:25  来源:  作者:

MySQL中没有Rank排名函数

当我们需要查询排名时,只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此,可不要小瞧基础而简单的查询语句,我们可以利用其来达到Rank函数一样的高级排名效果。

【MySQL】在MySQL中实现Rank高级排名函数

 

在这里我用一个简单例子来实现排名的查询:

创建高级排名查询的players表

CREATE TABLE `players` (
 `pid` int(2) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `age` int(2) NOT NULL,
 PRIMARY KEY (`pid`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `players` (`pid`, `name`, `age`) VALUES
(1, 'Samual', 25),
(2, 'Vino', 20),
(3, 'John', 20),
(4, 'Andy', 22),
(5, 'Brian', 21),
(6, 'Dew', 24),
(7, 'Kris', 25),
(8, 'William', 26),
(9, 'George', 23),
(10, 'Peter', 19),
(11, 'Tom', 20),
(12, 'Andre', 20);

1、在MySQL中实现Rank普通排名函数

在这里,我们希望获得一个排名字段的列,以及age的升序排列。所以我们的查询语句将是:

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age
| PID | NAME | AGE | RANK |
|-----|---------|-----|------|
| 10 | Peter | 19 | 1 |
| 12 | Andre | 20 | 2 |
| 2 | Vino | 20 | 3 |
| 3 | John | 20 | 4 |
| 11 | Tom | 20 | 5 |
| 5 | Brian | 21 | 6 |
| 4 | Andy | 22 | 7 |
| 9 | George | 23 | 8 |
| 6 | Dew | 24 | 9 |
| 7 | Kris | 25 | 10 |
| 1 | Samual | 25 | 11 |
| 8 | William | 26 | 12 |

要在mysql中声明一个变量,你必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化,而不需要单独的SET命令。当然,也可以使用SET,但它会处理两个查询:

SET @curRank := 0;
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players
ORDER BY age

2、查询以降序排列

首要按age的降序排列,其次按name进行排列,只需修改查询语句加上ORDER BY和 DESC以及列名即可。

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age DESC, name

结果

| PID | NAME | AGE | RANK |
|-----|---------|-----|------|
| 8 | William | 26 | 1 |
| 7 | Kris | 25 | 2 |
| 1 | Samual | 25 | 3 |
| 6 | Dew | 24 | 4 |
| 9 | George | 23 | 5 |
| 4 | Andy | 22 | 6 |
| 5 | Brian | 21 | 7 |
| 12 | Andre | 20 | 8 |
| 3 | John | 20 | 9 |
| 11 | Tom | 20 | 10 |
| 2 | Vino | 20 | 11 |
| 10 | Peter | 19 | 12 |

3、在MySQL中实现Rank普通并列排名函数

现在,如果我们希望为并列数据的行赋予相同的排名,则意味着那些在排名比较列中具有相同值的行应在MySQL中计算排名时保持相同的排名(例如在我们的例子中的age)。为此,我们使用了一个额外的变量。

SELECT pid, name, age, 
CASE 
WHEN @prevRank = age THEN @curRank 
WHEN @prevRank := age THEN @curRank := @curRank + 1
END AS rank
FROM players p, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY age

结果

| PID | NAME | AGE | RANK |
|-----|---------|-----|------|
| 10 | Peter | 19 | 1 |
| 12 | Andre | 20 | 2 |
| 2 | Vino | 20 | 2 |
| 3 | John | 20 | 2 |
| 11 | Tom | 20 | 2 |
| 5 | Brian | 21 | 3 |
| 4 | Andy | 22 | 4 |
| 9 | George | 23 | 5 |
| 6 | Dew | 24 | 6 |
| 7 | Kris | 25 | 7 |
| 1 | Samual | 25 | 7 |
| 8 | William | 26 | 8 |

如上所示,具有相同数据和排行的两行或多行,它们都会获得相同的排名。玩家Andre, Vino, John 和Tom都有相同的age,所以他们排名并列第二。下一个最高age的玩家(Brian)排名第3。这个查询相当于MSSQL和ORACLE 中的DENSE_RANK()函数

4、在MySQL中实现Rank高级并列排名函数

当使用RANK()函数时,如果两个或以上的行排名并列,则相同的行都会有相同的排名,但是实际排名中存在有关系的差距。

SELECT pid, name, age, rank FROM
(SELECT pid, name, age,
@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := age
FROM players p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY age) s

这是一个查询中的子查询。我们使用三个变量(@incRank,@prevRank,@curRank)来计算关系的情况下,在查询结果中我们已经补全了因为并列而导致的排名空位。我们已经封闭子查询到查询。这个查询相当于MSSQL和ORACLE中的RANK()函数。

| PID | NAME | AGE | RANK |
|-----|---------|-----|------|
| 10 | Peter | 19 | 1 |
| 12 | Andre | 20 | 2 |
| 2 | Vino | 20 | 2 |
| 3 | John | 20 | 2 |
| 11 | Tom | 20 | 2 |
| 5 | Brian | 21 | 6 |
| 4 | Andy | 22 | 7 |
| 9 | George | 23 | 8 |
| 6 | Dew | 24 | 9 |
| 7 | Kris | 25 | 10 |
| 1 | Samual | 25 | 10 |
| 8 | William | 26 | 12 |

在这里我们可以看到,Andre,Vino,John和Tom都有相同的age,所以他们排名并列第二。下一个最高年龄的球员(Brian)排名第6,而不是第3,因为有4个人并列排名在第2。
 

Tags:MySQL   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
概述操作系统及MySQL数据库的实时性能状态数据尤为重要,特别是在有性能抖动的时候,这些实时的性能数据可以快速帮助你定位系统或MySQL数据库的性能瓶颈,那么有哪些重要的实时性...【详细内容】
2019-05-14 09:28:25   点击:(3)  评论:(0)  加入收藏
在程序设计当中,我们很多场景下都会用 group by 关键字。比如在分页读取数据时,为了避免重复扫描记录,这就是必须要使用 group by 了。比如我们使用如下 DDL 创建表:CREATE TAB...【详细内容】
2019-05-14 09:28:25   点击:(8)  评论:(0)  加入收藏
方法一cmd 到mysql bin目录下用如下命令:mysqldump --opt -h192.168.0.156 -uusername -ppassword --skip-lock-tables databasename>database.sql把ip改成localhost就可以的...【详细内容】
2019-05-14 09:28:25   点击:(5)  评论:(0)  加入收藏
概述Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!这三种格式之间有什么区别呢?下面先介绍下各自的优缺点。ROW日志中会记录成每一行数据被修改的形式,然后在slav...【详细内容】
2019-05-14 09:28:25   点击:(6)  评论:(0)  加入收藏
MySQL中没有Rank排名函数当我们需要查询排名时,只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此,可不要小瞧基础而简单的查询语句,我们可以利用其来达到Rank函数...【详细内容】
2019-05-14 09:28:25   点击:(5)  评论:(0)  加入收藏
查看表是否被锁:直接在mysql命令行执行:show engine innodb statusG。查看造成死锁的sql语句,分析索引情况,然后优化sql.然后show processlist,查看造成死锁占用时间长的sql语...【详细内容】
2019-05-14 09:28:25   点击:(7)  评论:(0)  加入收藏
分片(类似分库)分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题。Shard这个词...【详细内容】
2019-05-14 09:28:25   点击:(7)  评论:(0)  加入收藏
mysql高并发的解决方法有:优化SQL语句,优化数据库字段,加缓存,分区表,读写分离以及垂直拆分,解耦模块,水平切分等。高并发大多的瓶颈在后台,在存储mysql的正常的优化方案如下:(1)代码中...【详细内容】
2019-05-14 09:28:25   点击:(11)  评论:(0)  加入收藏
数据库的索引就像一本书的目录一样,它可以快速定位你所需要的信息。下面来详细说一下MySQL的索引结构。常见索引类型Hash 索引Hash索引的底层实现是由Hash表来实现的,非常适合...【详细内容】
2019-05-14 09:28:25   点击:(3)  评论:(0)  加入收藏
在我们开发的过程中,使用全局锁和表锁的场景比较少,接触的也相对少一点,下面主要介绍一下。全局锁FTWRL全局锁就是对整个数据库实例加锁,MySQL 提供了 flush tables with read l...【详细内容】
2019-05-14 09:28:25   点击:(2)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门
'); })();