SQL查询统计未读消息导致结果异常的排查与解决

原查询功能 #

最初的SQL用于获取用户1的所有消息，按UserID分组并取每组最新消息，接收消息标记为MesRec，发送消息标记为MesSen，运行正常：

select *
from (SELECT 'MesRec' as Type, a.Message, a.UserFrom as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserFrom = b.UserFrom AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserFrom
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserTo = 1 AND b.DateSent IS NULL
UNION ALL
SELECT 'MesSen' as Type, a.Message, a.UserTo as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserTo = b.UserTo AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserTo
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserFrom = 1 AND b.DateSent IS NULL
) t
GROUP BY UserID

无未读计数时的正确结果示例：

Type | Message | UserID | Name | TeamID | TeamName | DateSent
MesRec | Last mess | 3 | Jimmy | 3 | Team 3 | 2022-08-28 19:06:14
MesRec | Test 1 | 4 | Alan | 4 | Team 4 | 2022-08-28 12:50:04
MesSen | Lorem | 5 | Sam | 5 | Team 5 | 2022-08-27 19:25:09

添加未读计数后的问题 #

为统计DateRead为空的未读消息数量，在子查询中添加sum(case when a.DateRead is null then 1 else 0 end) as Qty，修改后的查询如下：

select *
from (SELECT 'MesRec' as Type, a.Message, a.UserFrom as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, sum(case when a.DateRead is null then 1 else 0 end) as Qty
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserFrom = b.UserFrom AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserFrom
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserTo = 1 AND b.DateSent IS NULL
UNION ALL
SELECT 'MesSen' as Type, a.Message, a.UserTo as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, 0 as Qty
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserTo = b.UserTo AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserTo
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserFrom = 1 AND b.DateSent IS NULL
) t
GROUP BY UserID
ORDER BY DateSent DESC

此时出现两个问题：

• 未读数量匹配错误
• 无法获取最新消息（如用户3的最新接收消息丢失，被错误显示为发送消息）

错误结果示例：

Type | Message | UserID | Name | TeamID | TeamName | DateSent | Qty
MesSen | Test... | 3 | Jimmy | 3 | Team 3 | 2022-08-28 14:40:33 | 0
MesRec | Test 1 | 4 | Alan | 4 | Team 4 | 2022-08-28 12:50:04 | 1
MesSen | Lorem | 5 | Sam | 5 | Team 5 | 2022-08-27 19:25:09 | 0

修复后的查询 #

调整思路：将未读计数统计移到主查询中，同时为MesSen类型的消息设置NOW()作为DateRead值（不影响发送消息的未读统计），修正后的查询如下：

select Type, Message, UserID, Name, TeamID, TeamName, DateSent, sum(case when DateRead is null then 1 else 0 end) as Qty
from (SELECT 'MesRec' as Type, a.Message, a.UserFrom as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, a.DateRead
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserFrom = b.UserFrom AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserFrom
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserTo = 1 AND b.DateSent IS NULL
UNION ALL
SELECT 'MesSen' as Type, a.Message, a.UserTo as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, NOW() as DateRead
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserTo = b.UserTo AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserTo
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserFrom = 1 AND b.DateSent IS NULL
) t
GROUP BY UserID
ORDER BY DateSent DESC

修改后结果符合预期，既保留了每组最新消息的获取，又正确统计了未读消息数量。

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