获取与每位联系人最新聊天消息的SQL查询需求

需求说明 #

展示当前用户的联系人列表时，同步显示与每位联系人之间的最新聊天消息（无论当前用户是消息发送方还是接收方）。

数据库表结构 #

Users表 #

• id：用户ID（主键）
• name：用户名称
• email：用户邮箱
• created_at：创建时间
• updated_at：更新时间

Contacts表 #

• id：联系人记录ID（主键）
• user_id：关联用户ID（外键，关联Users.id）
• contact_id：联系人ID（外键，关联Users.id）
• created_at：创建时间
• updated_at：更新时间

Messages表 #

• id：消息ID（主键）
• sender_id：发送方用户ID（外键，关联Users.id）
• receiver_id：接收方用户ID（外键，关联Users.id）
• message：消息内容
• created_at：消息发送时间
• updated_at：消息更新时间

实现SQL查询 #

假设当前用户ID为1，可通过以下SQL得到所需结果：

SELECT
    u.name AS `Contact Name`,
    m.message AS `Latest Message`,
    m.created_at AS `Message Time`
FROM
    Contacts c
JOIN
    Users u ON c.contact_id = u.id
LEFT JOIN (
    SELECT
        CASE
            WHEN sender_id = 1 THEN receiver_id
            ELSE sender_id
        END AS contact_id,
        message,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY 
            CASE
                WHEN sender_id = 1 THEN receiver_id
                ELSE sender_id
            END 
            ORDER BY created_at DESC) AS rn
    FROM
        Messages
    WHERE
        sender_id = 1 OR receiver_id = 1
) m ON c.contact_id = m.contact_id AND m.rn = 1
WHERE
    c.user_id = 1
ORDER BY
    m.created_at DESC;

查询逻辑说明 #

1. 从Contacts表筛选当前用户（ID=1）的所有联系人记录
2. 关联Users表获取联系人的名称信息
3. 子查询筛选当前用户参与的所有消息，通过CASE语句统一标记每条消息对应的联系人ID，再用ROW_NUMBER()按联系人分组、消息时间倒序排序，取每组第一条（即最新消息）
4. 关联子查询结果，匹配联系人ID并仅保留最新消息数据
5. 最终按消息时间倒序排列结果

示例返回结果 #

内容的提问来源于stack exchange，提问作者Syed Azeem Haider