使用MySQL从数据表获取唯一数据：获取用户A与好友的最新消息

我来帮你搞定这个需求！首先得明确几个前提：你没给出message表的结构，我先按常见的消息表设计假设它包含sender（发送者ID）、receiver（接收者ID）、content（消息内容）、send_time（发送时间）这些核心字段。另外friend表的pending字段如果是用来标记好友请求是否通过的，我会在查询里默认只筛选已通过的好友（假设pending = 0代表已通过，你可以根据实际值调整）。

方法一：使用窗口函数（推荐，逻辑清晰） #

这种方法利用ROW_NUMBER()窗口函数对每个好友与A的消息按时间倒序排序，直接取第一条就是最新消息：

WITH user_a_friends AS (
    -- 先获取用户A的所有已通过好友
    SELECT 
        CASE 
            WHEN user1 = {userA_id} THEN user2 
            ELSE user1 
        END AS friend_id
    FROM friend
    WHERE (user1 = {userA_id} OR user2 = {userA_id})
      AND pending = 0 -- 筛选已通过的好友，可根据实际情况调整
),
friend_messages AS (
    -- 获取A与每个好友之间的所有消息
    SELECT 
        uf.friend_id,
        m.content,
        m.send_time,
        m.sender,
        m.receiver,
        -- 按好友分组，消息按时间倒序排名
        ROW_NUMBER() OVER (PARTITION BY uf.friend_id ORDER BY m.send_time DESC) AS rn
    FROM user_a_friends uf
    JOIN message m 
        ON (m.sender = {userA_id} AND m.receiver = uf.friend_id)
        OR (m.sender = uf.friend_id AND m.receiver = {userA_id})
)
-- 取每个好友的第一条（最新）消息
SELECT 
    friend_id,
    content AS last_message_content,
    send_time AS last_message_time,
    CASE WHEN sender = {userA_id} THEN '我发送的' ELSE '好友发送的' END AS message_direction
FROM friend_messages
WHERE rn = 1;

方法二：使用分组 + 子查询 #

如果你的数据库不支持窗口函数（比如老版本MySQL），可以用这种方式：

SELECT 
    -- 先确定好友ID
    CASE 
        WHEN f.user1 = {userA_id} THEN f.user2 
        ELSE f.user1 
    END AS friend_id,
    m.content AS last_message_content,
    m.send_time AS last_message_time,
    CASE WHEN m.sender = {userA_id} THEN '我发送的' ELSE '好友发送的' END AS message_direction
FROM friend f
JOIN message m 
    ON (m.sender = {userA_id} AND m.receiver = CASE WHEN f.user1 = {userA_id} THEN f.user2 ELSE f.user1 END)
    OR (m.sender = CASE WHEN f.user1 = {userA_id} THEN f.user2 ELSE f.user1 END AND m.receiver = {userA_id})
-- 关联子查询找到每个好友与A之间的最新消息时间
JOIN (
    SELECT 
        CASE 
            WHEN sender = {userA_id} THEN receiver 
            ELSE sender 
        END AS friend_id,
        MAX(send_time) AS latest_time
    FROM message
    WHERE sender = {userA_id} OR receiver = {userA_id}
    GROUP BY CASE WHEN sender = {userA_id} THEN receiver ELSE sender END
) latest_m 
    ON latest_m.friend_id = CASE WHEN f.user1 = {userA_id} THEN f.user2 ELSE f.user1 END
    AND m.send_time = latest_m.latest_time
WHERE (f.user1 = {userA_id} OR f.user2 = {userA_id})
  AND f.pending = 0;

关键说明 #

• 把{userA_id}替换成实际的用户A的ID；
• 如果pending字段不需要过滤（比如所有记录都是已通过好友），可以去掉AND pending = 0这个条件；
• 如果你需要更多消息字段（比如消息ID），直接在SELECT里添加对应的字段即可。

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