聊天应用SQL查询需求：获取指定用户会话及未读消息统计

表结构 #

UserConversation表（存储双向会话信息） #

User表（用户基础信息） #

UserProfile表（用户额外信息） #

UserMessage表（会话消息记录） #

期望输出 #

核心需求 #

• 在UserConversation表中筛选出JohnDoe（ID=1001）作为发送方或接收方的会话，返回会话对方的详细信息
• 统计每个会话中，JohnDoe作为接收方时未读消息（MessageRead=0）的数量
• 按会话内最新消息的CreateDate倒序排序

现有SQL代码 #

select 
    uc.Id as Id,
    us.Id as UserId,
    us.UserName as Username,
    ups.AvatarImage as UserAvatar
from UserConversation uc 
join UserMessage um on uc.Id = um.ConversationId
join [User] us on uc.SenderId = us.Id
join [User] ur on uc.RecipientId = ur.Id
join UserProfile ups on us.Id = ups.UserId
join UserProfile upr on ur.Id = upr.UserId
where uc.SenderId = 1001 or uc.RecipientId = 1001

完善后的SQL查询 #

SELECT
    uc.Id AS ConversationId,
    -- 动态获取会话对方ID：当前用户是发送方则取接收方ID，反之取发送方ID
    CASE 
        WHEN uc.SenderId = 1001 THEN uc.RecipientId 
        ELSE uc.SenderId 
    END AS UserId,
    u.UserName,
    up.Avatar AS UserAvatar,
    -- 获取会话内最新消息时间
    MAX(um.CreateDate) AS LatestDate,
    -- 统计当前用户作为接收方的未读消息数
    SUM(CASE 
        WHEN um.RecipientId = 1001 AND um.MessageRead = 0 THEN 1 
        ELSE 0 
    END) AS UnreadCount
FROM UserConversation uc
-- 关联消息表获取会话消息数据
LEFT JOIN UserMessage um ON uc.Id = um.ConversationId
-- 关联用户表获取对方信息
JOIN [User] u ON u.Id = CASE 
    WHEN uc.SenderId = 1001 THEN uc.RecipientId 
    ELSE uc.SenderId 
END
-- 关联用户资料表获取头像
JOIN UserProfile up ON up.UserId = u.Id
WHERE uc.SenderId = 1001 OR uc.RecipientId = 1001
-- 按会话和用户信息分组，确保每个会话仅返回一条记录
GROUP BY uc.Id, u.Id, u.UserName, up.Avatar
-- 按最新消息时间倒序排序
ORDER BY LatestDate DESC;

关键优化说明 #

1. 精准获取会话对方：用CASE表达式判断当前用户在会话中的角色，避免无效的用户表关联
2. 未读消息统计：通过SUM+CASE组合，仅计数当前用户作为接收方且未读的消息
3. 最新消息时间提取：用MAX(um.CreateDate)直接获取会话内的最新消息时间
4. 分组与排序：按会话ID和用户信息分组保证结果唯一性，最后按最新消息时间倒序排列

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