mysql中rank怎么使用mysql排名函数RANKDENSERANK

导读mysql排名函数RANK,DENSE_RANKRANK并列占位,DENSE_RANK并列不占位创建一张表income，里面有字段：id,年月yearMon....

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