时间戳

首页 > 解决方案 > MySQL 查询从满足用户定义条件的每个组中选择一条记录

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

问题描述

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

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 |
+----+------------+--------+

要返回每组中的最后一条记录，我们可以使用此处建议的方法：

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将产生上述输出的查询。

标签： mysqlsqlmariadb

解决方案

在 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;

推荐阅读