如何从SQLite的单聊与群聊会话中获取最新消息？

问题说明 #

在SQLite中无法同时获取单聊或群聊每个会话的最新消息，单聊的最新消息查询已实现，但群聊的最新消息始终无法正确获取。

数据库表结构 #

CREATE TABLE messages ( 
    sender_id TEXT, 
    receiver_id TEXT, 
    group_id TEXT, 
    message TEXT, 
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP 
);

测试数据 #

INSERT INTO messages (sender_id, receiver_id, group_id, message, timestamp) VALUES 
('user1', NULL, 'groupA', 'Hello, how are you?', '2023-11-04 12:00:00'), 
('user2', NULL, 'groupA', 'I am fine, thank you.', '2023-11-04 12:05:00'), 
('user3', 'user4', NULL, 'What is the weather like today?', '2023-11-04 12:10:00'), 
('user4', 'user3', NULL, 'It is sunny and warm.', '2023-11-04 12:15:00'), 
('user1', NULL, 'groupA', 'Can we meet tomorrow?', '2023-11-05 10:00:00'), 
('user2', NULL, 'groupA', 'Sure, what time?', '2023-11-05 10:05:00'), 
('user3', 'user4', NULL, 'Did you finish the report?', '2023-11-05 11:00:00'), 
('user4', 'user3', NULL, 'Almost, I''ll send it by evening.', '2023-11-05 11:05:00'), 
('user5', NULL, 'groupC', 'Let''s go for dinner tonight.', '2023-11-05 18:00:00'), 
('user6',NULL, 'groupC', 'Sounds good, where should we go?', '2023-11-05 18:05:00'), 
('user7', 'user8', NULL, 'I need help with this code.', '2023-11-06 09:00:00'), 
('user8', 'user7', NULL, 'Sure, send it over.', '2023-11-06 09:05:00'), 
('user1', NULL, 'groupD', 'Can you join the meeting tomorrow?', '2023-11-06 14:00:00'), 
('user3', NULL, 'groupD', 'Yes, I can make it.', '2023-11-06 14:05:00');

已实现的单聊最新消息查询 #

SELECT m1.*
FROM messages m1
INNER JOIN
(
    SELECT t.v1 AS sender_id, t.v2 AS receiver_id, MAX(t.timestamp) AS maxTime
    FROM
    (
        SELECT CASE WHEN sender_id < receiver_id THEN sender_id ELSE receiver_id END AS v1,
               CASE WHEN sender_id < receiver_id THEN receiver_id ELSE sender_id END AS v2,
               timestamp
        FROM messages
    ) t
    GROUP BY t.v1, t.v2
) m2
ON ((m1.sender_id = m2.sender_id AND m1.receiver_id = m2.receiver_id) OR (m1.sender_id = m2.receiver_id AND m1.receiver_id = m2.sender_id)) AND m1.timestamp = m2.maxTime

尝试过的错误查询（群聊返回随机消息） #

SELECT m1.*
FROM messages m1
INNER JOIN
(
    SELECT t.v1 AS sender_id, t.v2 AS receiver_id, MAX(t.timestamp) AS maxTime
    FROM
    (
        SELECT CASE WHEN sender_id < receiver_id THEN sender_id ELSE receiver_id END AS v1,
               CASE WHEN sender_id < receiver_id THEN receiver_id ELSE sender_id END AS v2,
               timestamp
        FROM messages
    ) t
    GROUP BY t.v1, t.v2
) m2
ON ((m1.sender_id = m2.sender_id AND m1.receiver_id = m2.receiver_id) OR (m1.sender_id = m2.receiver_id AND m1.receiver_id = m2.sender_id)) AND m1.timestamp = m2.maxTime 
UNION ALL 
SELECT *
FROM messages
WHERE group_id IS NOT NULL
GROUP BY group_id

期望输出 #

('user2', NULL, 'groupA', 'Sure, what time?', '2023-11-05 10:05:00'), 
('user4', 'user3', NULL, 'Almost, I''ll send it by evening.', '2023-11-05 11:05:00'), 
('user6',NULL, 'groupC', 'Sounds good, where should we go?', '2023-11-05 18:05:00'), 
('user8', 'user7', NULL, 'Sure, send it over.', '2023-11-06 09:05:00'), 
('user3', NULL, 'groupD', 'Yes, I can make it.', '2023-11-06 14:05:00')

正确解决方案 #

将单聊查询结果与群聊查询结果通过UNION ALL合并，群聊部分需先按group_id分组获取每个群的最新消息时间戳，再关联原表拿到对应消息：

-- 单聊最新消息
SELECT m1.*
FROM messages m1
INNER JOIN
(
    SELECT t.v1 AS sender_id, t.v2 AS receiver_id, MAX(t.timestamp) AS maxTime
    FROM
    (
        SELECT CASE WHEN sender_id < receiver_id THEN sender_id ELSE receiver_id END AS v1,
               CASE WHEN sender_id < receiver_id THEN receiver_id ELSE sender_id END AS v2,
               timestamp
        FROM messages
        WHERE group_id IS NULL  -- 仅处理单聊数据
    ) t
    GROUP BY t.v1, t.v2
) m2
ON ((m1.sender_id = m2.sender_id AND m1.receiver_id = m2.receiver_id) OR (m1.sender_id = m2.receiver_id AND m1.receiver_id = m2.sender_id)) AND m1.timestamp = m2.maxTime

UNION ALL

-- 群聊最新消息
SELECT m.*
FROM messages m
INNER JOIN (
    SELECT group_id, MAX(timestamp) AS max_time
    FROM messages
    WHERE group_id IS NOT NULL
    GROUP BY group_id
) g ON m.group_id = g.group_id AND m.timestamp = g.max_time

说明 #

原错误查询中，群聊部分直接GROUP BY group_id会触发SQLite的非标准行为——返回分组内的任意一条记录，而非最新的。通过先获取每个群的最大时间戳，再关联原表，就能精准定位到每个群的最新消息。

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