SQL使用GROUP BY分组时如何获取分组内的最新记录

问题根因 #

直接使用GROUP BY product_id时，在非严格SQL模式下数据库会默认返回分组内第一条匹配到的记录，无法保证返回的是分组内ID最大的最新记录，所以你拿到的是较早的id=488的记录，而非最新的id=46595的记录。

解决方案 #

方案1：MySQL 8.0及以上版本（窗口函数实现，逻辑清晰易维护） #

SELECT t.*
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY id DESC) AS rn
    FROM `peer_settings` 
    WHERE `sorting_hub_id` = '["172"]' -- 固定值匹配用=比LIKE查询效率更高
) t
WHERE t.rn = 1
ORDER BY t.product_id ASC;

逻辑说明：ROW_NUMBER() 函数按照product_id分组，每个分组内按id倒序排序，取排序为1的记录就是对应分组下id最大的最新记录。

方案2：MySQL 5.x及更低版本（无窗口函数兼容方案） #

SELECT ps.*
FROM `peer_settings` ps
INNER JOIN (
    SELECT product_id, MAX(id) AS max_id
    FROM `peer_settings`
    WHERE `sorting_hub_id` = '["172"]'
    GROUP BY product_id
) t ON ps.product_id = t.product_id AND ps.id = t.max_id
ORDER BY ps.product_id ASC;

逻辑说明：先通过子查询查出每个product_id分组下最大的id值，再关联原表拿到对应id的完整行数据，即可获取每个分组的最新记录。

优化建议 #

• 若sorting_hub_id的过滤规则为精确匹配固定字符串["172"]，无需使用LIKE，替换为=可大幅提升查询效率
• 查询频率较高的场景下，可以给sorting_hub_id、product_id、id字段添加联合索引，进一步优化查询速度

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