MSSQL如何仅当不存在特定子记录时左连接统计聊天室未读消息数

核心问题说明 #

你原有查询的逻辑错误在于WHERE条件的判断逻辑，会被其他用户的已读记录干扰：只要存在任意非目标用户的已读记录，哪怕目标用户本人已经读了这条消息，消息仍然会被计入未读统计。
未读消息的正确判定逻辑应为：单条消息不存在对应目标用户的已读关联记录，和其他用户是否已读完全无关。

修改后的完整查询语句 #

-- 替换为你需要查询的目标用户ID
DECLARE @TargetUserId UNIQUEIDENTIFIER = 'A97D66C4-014C-EC11-AE53-74D83E04F9D3'
-- 替换为对应应用ID
DECLARE @ApplicationId UNIQUEIDENTIFIER = '4ac752e9-004c-ec11-ae53-74d83e04f9d3'

SELECT 
    "chatRoom"."id" as id, 
    "chatRoom"."name" as name, 
    "chatRoom"."type" as type, 
    "chatRoom"."description" as description, 
    "chatRoom"."thumbnail" as thumbnail, 
    "chatRoom"."status" as status, 
    ISNULL(chats.unreadCount, 0) as unreadCount
FROM "chat_room" "chatRoom" 
-- 过滤用户是参与者的聊天室，直接用INNER JOIN即可，不需要LEFT JOIN
INNER JOIN "chat_room_participant" "participants" 
    ON "participants"."chatRoomId"="chatRoom"."id"  
    AND "participants"."userId" = @TargetUserId
LEFT JOIN (
    SELECT 
        c.chatRoomId,
        -- 计数没有对应已读记录的消息数，即为未读数
        COUNT(CASE WHEN cr.chatId IS NULL THEN 1 END) AS unreadCount
    FROM "chat" c
    -- 先拿到目标用户在各个聊天室的participantId
    LEFT JOIN "chat_room_participant" p 
        ON p.chatRoomId = c.chatRoomId 
        AND p.userId = @TargetUserId
    -- 仅关联目标用户自己的已读记录，完全排除其他用户的已读数据干扰
    LEFT JOIN "chat_read_by_chat_room_participant" cr 
        ON cr.chatId = c.id 
        AND cr.chatRoomParticipantId = p.id
    GROUP BY c.chatRoomId
) "chats" ON chats.chatRoomId = "chatRoom"."id" 
WHERE 
    ('ALL' = 'ALL' OR "chatRoom"."status" = 'ALL')
    AND "chatRoom"."applicationId" = @ApplicationId
ORDER BY "chatRoom"."lastUpdate" DESC

关键修改点说明 #

1. 把原来对readBy的全局过滤，改成只关联目标用户自己的已读记录，完全排除其他用户已读记录的干扰
2. 未读计数逻辑改为：只要消息和目标用户的已读关联记录不存在，就算未读，和其他用户的已读状态无关
3. 优化了participant的连接逻辑，用INNER JOIN替代LEFT JOIN，避免无意义的空记录扫描
4. 增加了ISNULL处理，没有消息的聊天室未读数默认返回0而不是NULL，更符合业务使用习惯

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