mysql group by取最新updatetime的记录|极客笔记

mysql group by取最新updatetime的记录

在实际开发中，我们经常会遇到需要查询数据库中最新的记录的情况。而当表中有多条记录存在对应相同id的情况下，我们可以使用GROUP BY和子查询来实现按条件取最新记录的查询。

创建示例数据表

首先我们创建一个示例数据表test_table来模拟我们的场景。表结构如下：

CREATE TABLE test_table (
    id INT,
    name VARCHAR(50),
    updatetime TIMESTAMP
);

INSERT INTO test_table (id, name, updatetime) VALUES
(1, 'Alice', '2022-01-01 12:00:00'),
(1, 'Alice', '2022-01-02 13:00:00'),
(2, 'Bob', '2022-01-01 14:00:00'),
(2, 'Bob', '2022-01-03 15:00:00'),
(3, 'Charlie', '2022-01-02 16:00:00');

使用子查询取最新记录

我们可以使用子查询来根据id分组，并取每组中updatetime最大的记录。具体SQL如下：

SELECT t1.id, t1.name, t1.updatetime
FROM test_table t1
JOIN (
    SELECT id, MAX(updatetime) AS max_time
    FROM test_table
    GROUP BY id
) t2
ON t1.id = t2.id AND t1.updatetime = t2.max_time;

解释一下上述SQL的执行过程：

首先我们在子查询中使用GROUP BY id分组，并使用MAX(updatetime)来取得每组中updatetime的最大值。
然后我们将子查询的结果与原表进行JOIN操作，并通过id和updatetime的匹配来取得最新的记录。

运行上述SQL语句，得到的结果为：

id  | name   | updatetime
--- | ------ | -------------------
1   | Alice  | 2022-01-02 13:00:00
2   | Bob    | 2022-01-03 15:00:00
3   | Charlie| 2022-01-02 16:00:00

思考

上面的方法虽然能够满足我们的需求，但是在数据量比较大的情况下效率可能较低。我们可以通过使用窗口函数来优化查询效率。

使用窗口函数优化

我们可以利用窗口函数ROW_NUMBER()和PARTITION BY来优化查询效率。具体SQL如下：

WITH cte AS (
    SELECT id, name, updatetime,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatetime DESC) AS rn
    FROM test_table
)
SELECT id, name, updatetime
FROM cte
WHERE rn = 1;

解释一下上述SQL的执行过程：