Mysql分组取最新一条记录

Mysql分组取最新一条记录

1. 数据准备

CREATE TABLE records (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    category_id INT NOT NULL,  
    data VARCHAR(255) NOT NULL,  
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  
);

-- 插入一些示例数据  
INSERT INTO records (category_id, data) VALUES  
(1, '这是分类1的第一条记录'),  
(2, '分类2的数据'),  
(1, '分类1的另一条记录'),  
(3, '第三条分类的数据'),  
(2, '分类2的更新数据'),  
(1, '分类1的最新记录'),  
(4, '新分类的数据');

1. 方法1：使用子查询获取每个组的最大时间戳，然后再次查询获取具体记录（如果时间戳是唯一的）

假设你有一个表records，其中包含字段id（主键，自增），category_id，data和created_at（记录创建时间）。

SELECT r1.*
FROM records r1
INNER JOIN (
    SELECT category_id, MAX(created_at) AS latest_created_at
    FROM records
    GROUP BY category_id
) r2 ON r1.category_id = r2.category_id AND r1.created_at = r2.latest_created_at;

这个查询首先在子查询中为每个category_id找到最新的created_at时间，然后主查询将这个时间与原始表中的记录进行匹配，以获取每个分组的最新的完整记录。
弊端：统一分组不能出现created_at相同的情况，否则分组不唯一

2. 方法2：使用窗口函数（MySQL 8.0+）

如果你的MySQL版本是8.0或更高，你可以使用窗口函数来简化查询。

WITH RankedRecords AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at DESC) AS rn
    FROM records
)
SELECT * FROM RankedRecords
WHERE rn = 1;

-- 或
SELECT
  * 
FROM
  ( SELECT *, ROW_NUMBER( ) OVER ( PARTITION BY category_id ORDER BY created_at DESC ) AS rn FROM records ) t 
WHERE
  t.rn =1

这里，ROW_NUMBER()窗口函数为每个category_id分组内的记录分配一个唯一的序号，基于created_at降序排列。因此，每个分组中created_at最新的记录将获得序号1。然后，通过在外层查询中选择序号为1的记录，你可以获取每个分组的最新记录。


弊端：虽然分组是唯一的，但是取的分组不是最新一条记录，任然要求时间戳是唯一

3. 方法3：使用LEFT JOIN（如果时间戳是唯一的）

如果每个category_id和created_at的组合是唯一的，你可以使用LEFT JOIN来实现：

SELECT r1.*
FROM records r1
LEFT JOIN records r2 
    ON r1.category_id = r2.category_id AND r1.created_at < r2.created_at
WHERE r2.id IS NULL;

这个查询尝试为r1中的每条记录找到同一category_id下但created_at更晚的记录（r2）。如果找不到这样的记录（即r2.id IS NULL），则r1中的记录就是该分组中的最新记录。

4. 方法4：如果ID是自增长的，并且与时间戳保持一致，则可以直接使用MAX(ID)

一般时间和主键id是正向关系，比如id大的插入时间就会比较大，我们可以以id为准来查询

SELECT * from records where id in(select  max(id) from records group by category_id)

-- 或

SELECT
  r.* 
FROM
  records r
  INNER JOIN ( SELECT category_id, max( id ) maxid FROM records GROUP BY category_id ) t ON t.category_id = t.category_id 
  AND r.id = t.maxid

5. 总结

1. 时间戳唯一的情况下1、2、3方法都能满足要求
2. 时间戳不唯一的情况下，考虑4方法，需要确认id是不是正向的