PostgreSQL查询：获取会话、最后消息及未读消息数

针对你的需求，我优化了原查询，解决了原查询可能存在的重复结果问题，同时新增了未读消息数的统计逻辑。假设三张表的核心字段如下：

• users: id（用户唯一标识）
• ads: id（广告ID）、owner_id（广告所有者用户ID）
• messages: id、ad_id（关联的广告ID）、sender（发送者用户ID）、receiver（接收者用户ID）、content（消息内容）、created_at（消息创建时间）、is_read（是否已读，布尔类型）

最终查询SQL #

WITH user_conversations AS (
    -- 筛选指定用户参与的合法会话，同时标记消息排序
    SELECT 
        ad.id AS conversation_ad_id,
        m.content AS last_message_content,
        m.created_at AS last_message_created_at,
        -- 为每个广告会话的消息按时间倒序排名，排名1的是最新消息
        ROW_NUMBER() OVER (PARTITION BY ad.id ORDER BY m.created_at DESC) AS message_rank
    FROM messages m
    JOIN ads ad ON m.ad_id = ad.id
    WHERE 
        -- 指定用户是消息的发送者或接收者
        (m.sender = '80d1c6070a7d' OR m.receiver = '80d1c6070a7d')
        -- 确保会话符合规则：发送者或接收者是广告所有者
        AND (m.sender = ad.owner_id OR m.receiver = ad.owner_id)
),
unread_stats AS (
    -- 统计每个会话中当前用户的未读消息数量
    SELECT 
        ad.id AS conversation_ad_id,
        COUNT(*) AS unread_message_count
    FROM messages m
    JOIN ads ad ON m.ad_id = ad.id
    WHERE 
        -- 未读消息是发给当前用户且未标记已读的
        m.receiver = '80d1c6070a7d'
        AND m.is_read = FALSE
        AND (m.sender = ad.owner_id OR m.receiver = ad.owner_id)
    GROUP BY ad.id
)
-- 整合会话最后消息与未读统计
SELECT 
    uc.conversation_ad_id,
    uc.last_message_content,
    uc.last_message_created_at,
    -- 没有未读消息时显示0
    COALESCE(us.unread_message_count, 0) AS unread_message_count
FROM user_conversations uc
LEFT JOIN unread_stats us ON uc.conversation_ad_id = us.conversation_ad_id
WHERE uc.message_rank = 1 -- 仅保留每个会话的最新消息
ORDER BY uc.last_message_created_at DESC
LIMIT 100 OFFSET 0;

关键改进点 #

1. 会话合法性校验：通过JOIN ads表，确保所有返回的会话都符合“发送者或接收者为广告所有者”的规则，原查询未做此校验。
2. 避免重复结果：使用ROW_NUMBER()窗口函数替代原查询的MAX(created_at)关联，避免同一时间多条消息导致的重复会话记录。
3. 未读消息统计：新增unread_stats CTE，统计每个会话中发给当前用户且未读的消息数量，通过LEFT JOIN和COALESCE确保无未读消息时显示0。
4. 可读性优化：用CTE拆分逻辑，替换旧版逗号连接表的写法，让查询结构更清晰。

如果你的messages表没有is_read字段，需要根据业务规则调整未读统计逻辑（比如默认所有对方发送的消息都是未读，直到用户标记），可以进一步修改unread_stats部分的条件。

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