MySQL中检索每组最后一条记录的方法

技术背景

在数据库操作中，经常会遇到需要从每组数据中检索出最后一条记录的需求。例如，在一个消息表中，可能需要获取每个用户的最后一条消息。在MySQL中，直接使用GROUP BY语句默认返回每组的第一条记录，无法满足获取最后一条记录的需求，因此需要采用其他方法来实现。

实现步骤

1. 使用窗口函数（MySQL 8.0及以上）

从MySQL 8.0开始支持窗口函数，可使用ROW_NUMBER()函数为每组记录编号，然后筛选出编号为1的记录。

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

2. 使用LEFT JOIN

通过LEFT JOIN将表自身连接，筛选出没有更大id的记录，即为每组的最后一条记录。

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

3. 使用子查询和MAX()函数

先通过子查询找出每组的最大id，然后从原表中筛选出这些id对应的记录。

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

4. 使用INNER JOIN和子查询

通过子查询找出每组的最大id，然后使用INNER JOIN连接原表和子查询结果。

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

核心代码

以下是几种常见方法的核心代码示例：

窗口函数法

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

LEFT JOIN法

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

子查询MAX()法

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

最佳实践

• 选择合适的方法：不同的方法在不同的数据量和数据分布下性能表现不同。例如，窗口函数法在MySQL 8.0及以上版本中简单直观，但在旧版本中不可用；LEFT JOIN法在某些情况下性能较好，但在数据量较大时可能较慢。
• 创建合适的索引：为GROUP BY和ORDER BY涉及的列创建索引可以提高查询性能。例如，对于上述示例，可在name和id列上创建索引。

CREATE INDEX idx_name_id ON messages (name, id);

常见问题

1. 性能问题

某些方法在数据量较大时可能会导致性能下降。例如，使用IN子查询在处理大量数据时可能会比较慢，可考虑使用JOIN替代。

2. 版本兼容性问题

窗口函数等新特性只在MySQL 8.0及以上版本支持，如果使用旧版本MySQL，需要采用其他方法。

3. 结果不一致问题

在使用某些方法时，可能会出现结果不一致的情况。例如，使用SELECT *, MAX(id) FROM messages GROUP BY name可能会返回任意数据，而不是MAX(id)对应的记录。