03-006 窗口函数rank,dense_rank,row_number 使用上的区别

学习排序类 rank,dense_rank,row_number使用上的区别

RANK：跳跃排序
DENSE_RANK：连续排序
row_number：没有重复值的排序[记录相等也是不重复的]可以进行分页使用。

1、创建表

/*
 Navicat Premium Data Transfer

 Source Server         : mariadb
 Source Server Type    : MariaDB
 Source Server Version : 100219
 Source Host           : 192.168.31.57:3306
 Source Schema         : my_db

 Target Server Type    : MariaDB
 Target Server Version : 100219
 File Encoding         : 65001

 Date: 18/10/2019 15:14:14
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(100) NOT NULL COMMENT '学号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '姓名',
  `subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '课程',
  `score` double(255, 0) NOT NULL COMMENT '成绩'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, '小明', '数学', 100);
INSERT INTO `test` VALUES (1, '小明', '语文', 90);
INSERT INTO `test` VALUES (1, '小明', '英语', 80);
INSERT INTO `test` VALUES (1, '小明', '物理', 100);
INSERT INTO `test` VALUES (1, '小明', '化学', 98);
INSERT INTO `test` VALUES (2, '张三', '数学', 90);
INSERT INTO `test` VALUES (2, '张三', '语文', 100);
INSERT INTO `test` VALUES (2, '张三', '英语', 80);
INSERT INTO `test` VALUES (2, '张三', '物理', 90);
INSERT INTO `test` VALUES (2, '张三', '化学', 70);
INSERT INTO `test` VALUES (3, '李四', '数学', 90);
INSERT INTO `test` VALUES (3, '李四', '语文', 92);
INSERT INTO `test` VALUES (3, '李四', '英语', 93);
INSERT INTO `test` VALUES (3, '李四', '物理', 94);
INSERT INTO `test` VALUES (3, '李四', '化学', 95);
INSERT INTO `test` VALUES (4, '王五', '数学', 40);
INSERT INTO `test` VALUES (4, '王五', '语文', 100);
INSERT INTO `test` VALUES (4, '王五', '英语', 70);
INSERT INTO `test` VALUES (4, '王五', '物理', 88);
INSERT INTO `test` VALUES (4, '王五', '化学', 96);

SET FOREIGN_KEY_CHECKS = 1;

2、案例

rank :跳跃式的排序

dense_rank:连续排序

row_number：没有重复值的排序[记录相等也是不重复的]可以进行分页使用

– rank：英语成绩80 排第几名：

MariaDB [my_db]> select * from test where subject = '英语' order by score desc;
+----+--------+---------+-------+
| id | name   | subject | score |
+----+--------+---------+-------+
|  3 | 李四   | 英语    |    93 |
|  1 | 小明   | 英语    |    80 |
|  2 | 张三   | 英语    |    80 |
|  4 | 王五   | 英语    |    70 |
+----+--------+---------+-------+
4 rows in set (0.00 sec)

MariaDB [my_db]> select * from (
    -> select *,rank() over(PARTITION by subject order by score desc) as 'rank' from test
    -> ) s where s.subject='英语' and s.score = 80;
+----+--------+---------+-------+------+
| id | name   | subject | score | rank |
+----+--------+---------+-------+------+
|  2 | 张三   | 英语    |    80 |    2 |
|  1 | 小明   | 英语    |    80 |    2 |
+----+--------+---------+-------+------+
2 rows in set (0.00 sec)

– dense_rank：查询每门课的前2名

MariaDB [my_db]> select name,subject,score from (select *,dense_rank()  over(partition by subject order by score desc) as 'rank' from test) t where t.rank<=2;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 小明   | 化学    |    98 |
| 王五   | 化学    |    96 |
| 小明   | 数学    |   100 |
| 张三   | 数学    |    90 |
| 李四   | 数学    |    90 |
| 小明   | 物理    |   100 |
| 李四   | 物理    |    94 |
| 李四   | 英语    |    93 |
| 张三   | 英语    |    80 |
| 小明   | 英语    |    80 |
| 王五   | 语文    |   100 |
| 张三   | 语文    |   100 |
| 李四   | 语文    |    92 |
+--------+---------+-------+
13 rows in set (0.00 sec)