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
,
