Mysql 分組排序的sql寫法

becage發表於2021-08-26
MySql排序

準備表

DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `total` int NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `purchases` VALUES (1, 'Joe', 5);
INSERT INTO `purchases` VALUES (2, 'Sally', 1);
INSERT INTO `purchases` VALUES (3, 'Joe', 2);
INSERT INTO `purchases` VALUES (4, 'Sally', 3);

錯誤寫法

SELECT * FROM purchases  GROUP BY customer ORDER BY total;

正確寫法

Mysql5.7

SELECT * FROM (SELECT * FROM purchases ORDER BY total desc limit 1000) as tmp GROUP BY tmp.customer;

Mysql8

WITH summary AS ( SELECT p.*, ROW_NUMBER() OVER ( PARTITION BY p.customer ORDER BY p.total DESC ) AS ranks FROM purchases p ) SELECT
* 
FROM
    summary 
WHERE
    ranks = 1;
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章