网站私信开发：如何获取无重复的用户消息关联列表？

看起来你在私信功能的消息列表查询上踩了几个小坑，我来帮你理清楚问题所在，然后给出针对性的解决方案：

先说说你当前SQL的核心问题 #

1. 表选错了：你写的是从users表查询，但users表并没有user1、user2、date这些字段，要获取消息列表应该从messages表查询才对。
2. DISTINCT无法解决重复对话：(user1=2, user2=1)和(user1=1, user2=2)在数据库里是两条完全不同的记录，DISTINCT没法识别这是同一个对话，所以去不掉重复。

解决方案：按对话合并，避免重复记录 #

根据你的需求（单个用户关联仅显示一条记录，也就是每个对话只展示一次），这里分两种常见场景给出SQL：

场景1：获取每个对话的最新消息（适合做对话列表） #

这个方案会把你和每个用户的对话合并成一条，显示该对话的最新消息，不会出现重复的对话条目：

SELECT 
    m.id,
    m.user1,
    m.user2,
    m.message,
    m.date
FROM messages m
INNER JOIN (
    -- 子查询：先按对话分组，获取每个对话的最新消息时间
    SELECT 
        GREATEST(user1, user2) AS user_a,
        LEAST(user1, user2) AS user_b,
        MAX(date) AS latest_date
    FROM messages
    WHERE user1 = $userid OR user2 = $userid
    GROUP BY user_a, user_b
) AS latest_msgs ON 
    -- 关联原表，找到对应最新时间的那条消息
    (m.user1 = latest_msgs.user_a AND m.user2 = latest_msgs.user_b AND m.date = latest_msgs.latest_date)
    OR (m.user1 = latest_msgs.user_b AND m.user2 = latest_msgs.user_a AND m.date = latest_msgs.latest_date)
WHERE m.user1 = $userid OR m.user2 = $userid
ORDER BY m.date DESC;

原理：用GREATEST和LEAST函数把对话双方的ID统一成“大ID+小ID”的固定组合，这样不管是你作为user1还是user2，同一个对话都会被归为一组，再通过分组取最新消息时间，最后关联原表拿到对应的消息内容。

场景2：直接合并对话，仅展示对话基本信息 #

如果只需要展示对话的参与方、最新时间和最新消息内容，不需要每条消息的详细记录，可以用更简洁的分组查询：

SELECT 
    GREATEST(user1, user2) AS user_a,
    LEAST(user1, user2) AS user_b,
    MAX(date) AS latest_date,
    -- 取最新的一条消息内容
    SUBSTRING_INDEX(GROUP_CONCAT(message ORDER BY date DESC), ',', 1) AS latest_message
FROM messages
WHERE user1 = $userid OR user2 = $userid
GROUP BY user_a, user_b
ORDER BY latest_date DESC;

原理：同样用GREATEST和LEAST统一对话分组，然后用MAX(date)取最新消息时间，GROUP_CONCAT结合SUBSTRING_INDEX提取最新的消息内容。

额外提示：关联用户信息显示对方用户名 #

如果要显示对话对方的用户名，可以关联users表，比如在场景1的基础上修改：

SELECT 
    m.id,
    m.user1,
    m.user2,
    m.message,
    m.date,
    -- 取对方的用户名
    CASE 
        WHEN m.user1 = $userid THEN u2.username
        ELSE u1.username
    END AS partner_username
FROM messages m
INNER JOIN users u1 ON m.user1 = u1.id
INNER JOIN users u2 ON m.user2 = u2.id
INNER JOIN (
    SELECT 
        GREATEST(user1, user2) AS user_a,
        LEAST(user1, user2) AS user_b,
        MAX(date) AS latest_date
    FROM messages
    WHERE user1 = $userid OR user2 = $userid
    GROUP BY user_a, user_b
) AS latest_msgs ON 
    (m.user1 = latest_msgs.user_a AND m.user2 = latest_msgs.user_b AND m.date = latest_msgs.latest_date)
    OR (m.user1 = latest_msgs.user_b AND m.user2 = latest_msgs.user_a AND m.date = latest_msgs.latest_date)
WHERE m.user1 = $userid OR m.user2 = $userid
ORDER BY m.date DESC;

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