如何用单条SQL查询获取登录用户u1的最新对话对象详情

首先，咱们先聊聊你之前尝试的SQL为啥没得到预期结果：

• 子查询里同时返回了receiver_id和sender_id两列，但外层查询的id=只能接受单个值，这会直接导致语法错误或者返回错误结果
• where receiver_id or sender_id=1这个条件逻辑不对，正确的写法应该是筛选出u1发送或接收的消息，也就是where sender_id=1 OR receiver_id=1

下面给你两种可行的单条SQL方案，都能精准实现需求：

方案一：先定位最新消息，关联用户表获取详情 #

SELECT u.*
FROM Users u
JOIN (
    -- 先筛选u1参与的消息，取最新的那条并确定对话对象ID
    SELECT 
        CASE 
            WHEN sender_id = 1 THEN receiver_id 
            ELSE sender_id 
        END AS chat_partner_id
    FROM Messages
    WHERE sender_id = 1 OR receiver_id = 1
    ORDER BY send_time DESC
    LIMIT 1
) latest_chat ON u.id = latest_chat.chat_partner_id;

方案二：用子查询直接匹配对话对象ID #

SELECT *
FROM Users
WHERE id = (
    SELECT 
        IF(sender_id = 1, receiver_id, sender_id)
    FROM Messages
    WHERE sender_id = 1 OR receiver_id = 1
    ORDER BY send_time DESC
    LIMIT 1
);

核心逻辑说明： #

1. 先筛选出所有u1（id=1）参与的消息（不管是发送还是接收）
2. 按send_time倒序排序，取第一条就是最新的对话记录
3. 通过CASE或IF函数判断对话对象：如果这条消息是u1发的，对象就是接收者；如果是u1收的，对象就是发送者
4. 最后用这个对象ID关联Users表，拿到完整的用户信息

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