MySQL查询：筛选用户对话中同身份组合的最新消息记录

我有一张聊天数据表：

规则说明：sender_name或receiver_name为NULL表示发送者或接收者匿名：

• Jack匿名给Amy发送消息的存储示例：

• Jack实名给Amy发送消息的存储示例：

需要筛选出每一组「sender_id、sender_name、receiver_id、receiver_name」完全相同的记录中idx最大的最新条目，期望结果如下：

注：原问题给出的期望结果遗漏了idx=5的记录，该记录是sender_name=jack、sender_id=jackID、receiver_name=NULL、receiver_id=amyID组的最新条目。

使用窗口函数ROW_NUMBER()可以高效实现需求，核心思路是按指定字段分组后对idx降序排序，取每组的第一条记录。需要注意SQL中NULL的比较特性：NULL = NULL不成立，因此分组时需用COALESCE()将NULL转换为统一占位符，确保相同匿名状态的记录能被正确分组。

SQL 查询语句 #

WITH ranked_chat_records AS (
    SELECT
        idx,
        sender_name,
        sender_id,
        receiver_name,
        receiver_id,
        ROW_NUMBER() OVER (
            PARTITION BY
                sender_id,
                COALESCE(sender_name, '[ANONYMOUS]'),
                receiver_id,
                COALESCE(receiver_name, '[ANONYMOUS]')
            ORDER BY idx DESC
        ) AS row_rank
    FROM chat_table
)
SELECT idx, sender_name, sender_id, receiver_name, receiver_id
FROM ranked_chat_records
WHERE row_rank = 1;

关键说明 #

1. 分组逻辑：通过PARTITION BY按sender_id、处理后的sender_name、receiver_id、处理后的receiver_name分组，COALESCE(sender_name, '[ANONYMOUS]')将NULL替换为占位符，解决NULL无法相等比较的问题。
2. 排序与排名：组内按idx降序排列，最新的记录排名为1。
3. 结果筛选：只保留排名为1的记录，即每组的最新条目。

额外注意 #

• 若数据库对大小写不敏感（如MySQL默认配置），JackID和jackID会被视为同一ID，如需严格区分大小写，需将字段字符集设置为区分大小写类型（如utf8_bin）。
• 占位符[ANONYMOUS]可根据实际业务调整，只要不与真实用户名冲突即可。

内容的提问来源于stack exchange，提问作者서하연