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;

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

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;