如何通过单条SQLite查询获取会话最后消息与未读消息数

看起来你遇到的核心问题是会话的双向配对分组（比如AA↔JD应该算同一个会话，但直接按SentBy和SentTo分组会拆成两个），以及未读消息数的统计。下面是针对你需求的完整解决方案：

核心思路 #

要实现目标，我们需要：

• 生成不区分收发方向的唯一会话标识，确保AA-JD和JD-AA被归为同一个会话
• 找到每个会话的最新消息（按sentTime最大的那条）
• 统计每个会话中当前用户的未读消息（即SentTo为当前用户且readTime IS NULL的消息数）

完整SQL查询语句 #

假设你的当前登录用户是@currentUser（比如JD），表名为messages，字段名和你给出的一致，查询语句如下：

WITH session_last_msg AS (
    -- 第一步：生成会话唯一标识，并找到每个会话的最新sentTime
    SELECT
        -- 生成不区分方向的会话键：把收发方按字典序拼接，确保AA-JD和JD-AA是同一个键
        CASE WHEN SentBy < SentTo THEN SentBy || '-' || SentTo ELSE SentTo || '-' || SentBy END AS session_key,
        MAX(sentTime) AS latest_sentTime
    FROM messages
    -- 过滤出和当前用户相关的会话（可选，如果你只需要当前用户的会话）
    WHERE SentBy = @currentUser OR SentTo = @currentUser
    GROUP BY session_key
),
unread_counts AS (
    -- 第二步：统计每个会话的未读消息数（当前用户作为接收方且未读）
    SELECT
        CASE WHEN SentBy < SentTo THEN SentBy || '-' || SentTo ELSE SentTo || '-' || SentBy END AS session_key,
        COUNT(*) AS unread_count
    FROM messages
    WHERE SentTo = @currentUser AND readTime IS NULL
    GROUP BY session_key
)
-- 第三步：关联原表获取最新消息详情，同时关联未读计数
SELECT
    m.*,
    COALESCE(uc.unread_count, 0) AS unread_count
FROM messages m
JOIN session_last_msg slm 
    ON (CASE WHEN m.SentBy < m.SentTo THEN m.SentBy || '-' || m.SentTo ELSE m.SentTo || '-' || m.SentBy END) = slm.session_key
    AND m.sentTime = slm.latest_sentTime
LEFT JOIN unread_counts uc
    ON (CASE WHEN m.SentBy < m.SentTo THEN m.SentBy || '-' || m.SentTo ELSE m.SentTo || '-' || m.SentBy END) = uc.session_key
-- 可选：如果需要只保留当前用户的会话
WHERE m.SentBy = @currentUser OR m.SentTo = @currentUser
ORDER BY m.sentTime DESC;

关键部分解释 #

1. 会话唯一标识生成：
   使用CASE WHEN SentBy < SentTo THEN SentBy || '-' || SentTo ELSE SentTo || '-' || SentBy END，这样不管消息是AA→JD还是JD→AA，都会生成同一个session_key（比如AA-JD），确保会话分组正确。

2. 获取最后一条消息：
   通过CTE session_last_msg先找到每个会话的最大sentTime，再关联原表messages，就能精准定位到该会话的最后一条消息。

3. 未读消息数统计：
   另一个CTE unread_counts统计每个会话中，当前用户作为接收方（SentTo = @currentUser）且readTime IS NULL的消息数量，用LEFT JOIN关联主查询，确保没有未读消息的会话显示0而不是NULL（通过COALESCE处理）。

4. 过滤当前用户的会话：
   如果你只需要当前登录用户参与的会话，可以保留WHERE SentBy = @currentUser OR SentTo = @currentUser条件，避免查询无关会话。

针对你原有问题的改进 #

• 原查询GROUP BY SentBy, SentTo会把AA-JD和JD-AA当成两个独立分组，而上面的方法通过统一会话键解决了这个问题。
• 新增了未读消息数的统计逻辑，直接整合在同一条查询中，符合你监听SQLite数据库的需求。

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