mysql排名函数RANK,DENSE_RANK

mysql排名函数RANK,DENSE_RANK

RANK并列占位

,DENSE_RANK并列不占位

创建一张表income，里面有字段：id,年月yearMonth，地区area和金额amount。
执行如下SQL语句，进行导入数据。

CREATE TABLE `income` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `yearMonth` char(6) DEFAULT NULL,
  `area` varchar(20) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入所要的数据

INSERT INTO `income` VALUES ('0000000001', '202101', '贵州', '897.00');
INSERT INTO `income` VALUES ('0000000002', '202102', '广西', '3218.90');
INSERT INTO `income` VALUES ('0000000003', '202103', '广西', '437.00');
INSERT INTO `income` VALUES ('0000000004', '202103', '贵州', '3965.00');
INSERT INTO `income` VALUES ('0000000005', '202102', '贵州', '456.87');
INSERT INTO `income` VALUES ('0000000006', '202101', '广西', '3486.56');
INSERT INTO `income` VALUES ('0000000007', '202104', '贵州', '897.00');
INSERT INTO `income` VALUES ('0000000008', '202104', '广西', '437.00');

使用函数查询语句如下：

SELECT *
	,rank() over(PARTITION by area  ORDER BY amount desc) rank11-- 并列占位
	,dense_rank() over(PARTITION by area ORDER BY amount desc) dense_rank11-- 并列不占位
from income

查询结果如下图：

mysql 8.0.22

2021-7-21