如何在MySQL中按分组筛选每个id最后一条stat为ON的记录

我来帮你搞定这个问题！先理清楚你的场景：咱们有个test表，记录了每个ID的状态变更时间，现在要找出每个ID的最后一条记录，而且只保留那些最终状态是ON的条目。

先回顾你的表结构和测试数据 #

表结构：

CREATE TABLE test (id int(10), stat varchar(20), regdt datetime);

测试数据：

INSERT INTO test VALUES 
(10001,'ON', '2020-06-29 01:00:00'), 
(10002,'ON', '2020-06-29 02:00:00'), 
(10003,'ON', '2020-06-29 03:00:00'), 
(10003,'OFF','2020-06-29 04:00:00'), 
(10004,'ON', '2020-06-29 05:00:00'), 
(10004,'OFF','2020-06-29 06:00:00'), 
(10004,'ON', '2020-06-29 07:00:00');

推荐解决方案（支持MySQL 8.0+及其他现代数据库） #

用窗口函数ROW_NUMBER()是最直观清晰的方式，它能给每个ID的记录按时间倒序编号，最新的那条编号就是1，之后我们只需要筛选编号为1且状态是ON的记录就行：

SELECT id, stat, regdt
FROM (
    SELECT 
        id, 
        stat, 
        regdt,
        -- 按ID分组，每组内按时间倒序排列，最新记录编号为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY regdt DESC) AS rn
    FROM test
) t
-- 取每组最新的，且状态为ON的记录
WHERE rn = 1 AND stat = 'ON';

执行这个查询后，就能得到你想要的结果：

兼容MySQL 5.x的替代方案 #

如果你的数据库版本比较旧，不支持窗口函数，可以用关联子查询的方式：先找出每个ID的最新记录时间，再关联原表找到对应记录，最后筛选状态：

SELECT t1.id, t1.stat, t1.regdt
FROM test t1
INNER JOIN (
    -- 先获取每个ID的最新记录时间
    SELECT id, MAX(regdt) AS max_regdt
    FROM test
    GROUP BY id
) t2 ON t1.id = t2.id AND t1.regdt = t2.max_regdt
-- 筛选状态为ON的记录
WHERE t1.stat = 'ON';

这个方法同样能得到预期结果，适合不支持窗口函数的老版本数据库。

内容的提问来源于stack exchange，提问作者user1819769