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