MySQL 查询从满足用户定义条件的每个组中选择一条记录

Question

我正在寻找一个查询，该查询从满足用户定义标准的每个组中选择一条记录。我可以用下面的说明表来解释:

CREATE TABLE sample_table
(
  id INT UNSIGNED AUTO_INCREMENT,
  categoryID INT,
  weight INT,

  PRIMARY KEY(id)  
);

INSERT INTO sample_table(categoryID, weight) VALUES(1, 3), (1, 5), (1, 2), (2, 5), (2, 3), (2, 9), (3, 5), (3, 3), (3, 3);

一个简单的 GROUP BY categoryID 查询返回每个组中的第一条记录，如下所示:

SELECT * FROM sample_table GROUP BY categoryID;
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  1 |          1 |      3 |
|  4 |          2 |      5 |
|  7 |          3 |      5 |
+----+------------+--------+

要返回每个组中的最后一条记录，我们可以使用 suggested here 方法:

SELECT * FROM sample_table WHERE id IN (SELECT MAX(id) FROM sample_table GROUP BY categoryID);
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  3 |          1 |      2 |
|  6 |          2 |      9 |
|  9 |          3 |      3 |
+----+------------+--------+

但是，我想做的是在每组中选择weight 字段值最高的记录。因此，我的输出应该是:

+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  2 |          1 |      5 |
|  6 |          2 |      9 |
|  7 |          3 |      3 |
+----+------------+--------+

---

请建议将产生上述输出的GROUP BY categoryID 查询。

Answer 1

在 MySQL 8+ 上执行此操作的“现代”方法是使用 ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY categoryID ORDER BY weight DESC) rn
    FROM sample_table
)

SELECT id, categoryID, weight
FROM cte
WHERE rn = 1;

在早期版本中，您可以使用连接方法:

SELECT t1.id, t1.categoryID, t1.weight
FROM sample_table t1
INNER JOIN
(
    SELECT categoryID, MAX(weight) AS max_weight
    FROM sample_table
    GROUP BY categoryID
) t2
    ON t2.categoryID = t1.categoryID AND
       t2.max_weight = t1.weight;