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