SQL按order_id分组统计时如何获取每组最新id对应数据

实现方案 #

你的原始SQL不符合标准SQL规范，非聚合列未放入GROUP BY子句，仅在低版本MySQL关闭ONLY_FULL_GROUP_BY配置时可执行，返回的首条记录本身就是不稳定的。你可以根据数据库版本选择以下两种方案实现需求：

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）

WITH group_stat AS (
    SELECT 
        *,
        COUNT(id) OVER (PARTITION BY order_id) AS `count`,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY id DESC) AS rn
    FROM chat_notif 
    WHERE sent_to = 2
)
SELECT id, order_id, chat_id, sent_to, `count`
FROM group_stat
WHERE rn = 1;

逻辑说明：先用COUNT() OVER窗口函数按order_id分组计算每组总记录数，再用ROW_NUMBER()给同组记录按id倒序编号，取编号为1的就是每组id最大的最新记录。

方案2：兼容不支持窗口函数的数据库（如MySQL 5.x）

SELECT 
    c.*,
    g.`count`
FROM chat_notif c
INNER JOIN (
    SELECT 
        order_id,
        MAX(id) AS max_id,
        COUNT(id) AS `count`
    FROM chat_notif
    WHERE sent_to = 2
    GROUP BY order_id
) g ON c.order_id = g.order_id AND c.id = g.max_id
WHERE c.sent_to = 2;

逻辑说明：先在子查询中按order_id分组，提前算出每组的最大id和记录总数，再关联原表拿到最大id对应的完整行数据即可。

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