获取用户列表及与当前用户的最后聊天消息SQL问题

我有两张数据表：

• users表：存储用户基础信息，主键为user_id
• chat表：存储聊天消息，主键为msg_id；incoming_msg_id和outgoing_msg_id均关联users.user_id；read_at字段为NULL时表示消息未读

需要实现：列出除当前登录用户（user_id=?）外的所有用户，同时展示每个用户与当前用户的最后一条聊天消息（无消息则为空）。

我尝试了两条SQL语句，但结果存在错误——会显示目标用户与其他人的聊天消息，而非仅和当前用户的对话，请求修正。

错误SQL语句1 #

SELECT user_id, username, image, status, active,
CASE WHEN status IN ("Active Now") THEN true ELSE false END AS isonline,
(SELECT msg 
FROM chat
WHERE
(incoming_msg_id = users.user_id) OR
(outgoing_msg_id = users.user_id)
ORDER BY msg_id DESC LIMIT 1
) AS last_msg,

(SELECT msg_id
FROM chat
WHERE
(incoming_msg_id = users.user_id) OR
(outgoing_msg_id = users.user_id)
ORDER BY msg_id DESC LIMIT 1 ) AS last_msg_id,

(SELECT outgoing_msg_id
FROM chat
WHERE msg_id = last_msg_id
) AS outgoing_msg_id,

(SELECT incoming_msg_id
FROM chat
WHERE msg_id = last_msg_id
) AS incoming_msg_id,

(SELECT created_at
FROM chat
WHERE msg_id = last_msg_id
) AS last_interaction, 

(SELECT IF(read_at = null, false, true)
FROM chat
WHERE msg_id = last_msg_id
) AS has_unread_msg,

(SELECT read_at
FROM chat
WHERE msg_id = last_msg_id
) AS read_at,

(SELECT IF(user_id = incoming_msg_id, true, false) AS isme
FROM chat
WHERE msg_id = last_msg_id
) AS self 

FROM users
WHERE
(NOT user_id = ?) AND 
(users.username LIKE ?)
ORDER BY active DESC, status ASC, has_unread_msg DESC, last_interaction DESC

错误SQL语句2 #

WITH 
cte1 AS (
  SELECT *, incoming_msg_id as user_id FROM chat
  UNION ALL
  SELECT *, outgoing_msg_id FROM chat
  ),
cte2 AS (
  SELECT *, IF(ISNULL(read_at), true, false) AS has_unread_msg, msg AS last_msg, IF(user_id = incoming_msg_id, true, false) AS self, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
  FROM cte1
  )
SELECT *, IF(status = "Active now", true, false) AS isonline
FROM users
JOIN cte2 USING (user_id)
WHERE rn = 1

表结构与测试数据 #

chat表 #

CREATE TABLE `chat` (
  `msg_id` int(11) NOT NULL,
  `incoming_msg_id` int(255) NOT NULL,
  `outgoing_msg_id` int(255) NOT NULL,
  `msg` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `chat` (`msg_id`, `incoming_msg_id`, `outgoing_msg_id`, `msg`, `read_at`, `created_at`) VALUES
(3, 1, 5, 'no way', '2022-10-23 17:53:13', '2022-10-23 17:53:13'),
(4, 1, 5, 'I am sitting here and nothing happens!', '2022-10-23 17:53:13', '2022-10-23 17:53:13'),
(5, 1, 5, 'Still sitting here...', '2022-10-23 17:53:13', '2022-10-23 17:53:13'),
(9, 5, 1, 'Hi there', '2022-10-23 16:13:25', '2022-10-23 16:13:25'),
(45, 5, 25, 'Hey there. How is ya doing??', '2022-10-22 08:16:18', '2022-10-22 08:16:18'),
(46, 3, 25, 'hello there! Are you available?', '2022-10-23 18:16:08', '2022-10-23 18:16:08'),
(47, 3, 25, 'Something?', '2022-10-23 18:16:08', '2022-10-23 18:16:08'),
(48, 1, 25, 'hi!', '2022-10-24 11:45:13', '2022-10-24 11:45:13'),
(49, 1, 25, 'hi!', '2022-10-24 11:45:13', '2022-10-24 11:45:13'),
(63, 25, 5, 'nothing special here!', '2022-10-23 21:29:04', '2022-10-23 21:29:04'),
(64, 5, 25, 'What if we meet next week?', '2022-10-22 08:16:18', '2022-10-22 08:16:18'),
(65, 5, 25, 'What if we meet next week?', '2022-10-22 08:16:18', '2022-10-22 08:16:18'),
(365, 2, 5, 'sechs', NULL, '2022-10-21 20:49:09'),
(366, 2, 5, 'sieben', NULL, '2022-10-21 20:49:10'),
(367, 2, 5, 'acht', NULL, '2022-10-21 20:50:18'),
(368, 2, 5, 'neun', NULL, '2022-10-21 20:50:19');

msg_id为自增主键。

users表 #

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET armscii8 NOT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `active` tinyint(1) DEFAULT 0,
  `status` varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Not active',
  `last_chat_notification_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

当前登录用户user_id为25。

修正后的SQL语句 #

SET @current_user = 25; -- 替换为当前登录用户的user_id

WITH user_chats AS (
    -- 筛选当前用户与其他用户的所有对话记录
    SELECT 
        msg_id,
        incoming_msg_id,
        outgoing_msg_id,
        msg,
        read_at,
        created_at,
        -- 获取对话的另一方用户ID
        CASE 
            WHEN incoming_msg_id = @current_user THEN outgoing_msg_id
            ELSE incoming_msg_id
        END AS other_user_id,
        -- 标记消息是否由当前用户发送
        outgoing_msg_id = @current_user AS isme,
        -- 标记是否为当前用户的未读消息
        CASE 
            WHEN incoming_msg_id = @current_user AND read_at IS NULL THEN TRUE
            ELSE FALSE
        END AS has_unread_msg
    FROM chat
    WHERE incoming_msg_id = @current_user OR outgoing_msg_id = @current_user
),
latest_chats AS (
    -- 为每个用户筛选出与当前用户的最后一条对话
    SELECT 
        other_user_id,
        msg AS last_msg,
        msg_id AS last_msg_id,
        incoming_msg_id,
        outgoing_msg_id,
        created_at AS last_interaction,
        read_at,
        isme,
        -- 只要该用户有未读消息，就标记为true
        MAX(has_unread_msg) OVER (PARTITION BY other_user_id) AS has_unread_msg
    FROM user_chats
    QUALIFY ROW_NUMBER() OVER (PARTITION BY other_user_id ORDER BY msg_id DESC) = 1
)
SELECT 
    u.user_id,
    u.username,
    u.image,
    u.status,
    u.active,
    u.status = 'Active Now' AS isonline,
    COALESCE(lc.last_msg, '') AS last_msg,
    lc.last_msg_id,
    lc.outgoing_msg_id,
    lc.incoming_msg_id,
    lc.last_interaction,
    COALESCE(lc.has_unread_msg, FALSE) AS has_unread_msg,
    lc.read_at,
    COALESCE(lc.isme, FALSE) AS self
FROM users u
LEFT JOIN latest_chats lc ON u.user_id = lc.other_user_id
WHERE u.user_id != @current_user
  AND u.username LIKE '%' -- 替换为实际的搜索匹配条件
ORDER BY 
    u.active DESC,
    u.status ASC,
    has_unread_msg DESC,
    last_interaction DESC;

错误原因说明 #

1. 错误SQL1：子查询仅过滤了包含目标用户ID的消息，但未限定这些消息必须是目标用户与当前用户之间的对话，因此会取出目标用户和其他第三方的聊天记录。
2. 错误SQL2：CTE1通过UNION ALL将每条消息的收发方都作为user_id，没有筛选仅和当前用户相关的对话；同时分区逻辑错误，导致取到的不是目标用户与当前用户的最后一条消息。

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