MS SQL Server 2008查询无法返回完整收发消息计数结果求助

我看了你的查询和测试数据，原查询的问题很明确：它只从user1作为消息接收方的会话记录里关联用户（WHERE C.ToUserId = 'user1'），所以只能拿到给user1发过消息的用户（比如测试数据里的user2、user4），但漏掉了那些user1主动发送过消息但对方没回复的用户（比如user3）。

下面是两种修改后的查询方案，都能返回所有和user1有过消息往来的用户记录：

方案一：用EXISTS筛选关联用户 #

这种方式直接从用户表出发，筛选出所有和user1有交互的用户，再分别统计发送/接收数量：

SELECT 
    U.UserId AS 'Id', 
    U.Name AS 'Name',
    (SELECT COUNT(*) FROM [Conversation] WHERE FromUserId = 'user1' AND ToUserId = U.UserId) AS 'SentCount',
    (SELECT COUNT(*) FROM [Conversation] WHERE ToUserId = 'user1' AND FromUserId = U.UserId) AS 'ReceivedCount'
FROM [User] U
WHERE 
    U.UserId <> 'user1' -- 排除user1自己
    AND (
        -- 该用户给user1发过消息
        EXISTS (SELECT 1 FROM [Conversation] WHERE FromUserId = U.UserId AND ToUserId = 'user1')
        OR 
        -- user1给该用户发过消息
        EXISTS (SELECT 1 FROM [Conversation] WHERE ToUserId = U.UserId AND FromUserId = 'user1')
    )
ORDER BY U.UserId;

方案二：用CTE+LEFT JOIN统计（更高效，适合大数据量） #

这种方式先通过UNION获取所有相关用户ID，再分别统计发送/接收数量，用LEFT JOIN确保数量为0时也能显示为0：

WITH RelatedUsers AS (
    -- 给user1发过消息的用户
    SELECT DISTINCT FromUserId AS UserId FROM [Conversation] WHERE ToUserId = 'user1'
    UNION
    -- user1发过消息的用户
    SELECT DISTINCT ToUserId AS UserId FROM [Conversation] WHERE FromUserId = 'user1'
)
SELECT 
    U.UserId AS 'Id',
    U.Name AS 'Name',
    COALESCE(Sent.SentCount, 0) AS 'SentCount',
    COALESCE(Received.ReceivedCount, 0) AS 'ReceivedCount'
FROM RelatedUsers RU
JOIN [User] U ON RU.UserId = U.UserId
LEFT JOIN (
    SELECT ToUserId, COUNT(*) AS SentCount 
    FROM [Conversation] 
    WHERE FromUserId = 'user1'
    GROUP BY ToUserId
) Sent ON RU.UserId = Sent.ToUserId
LEFT JOIN (
    SELECT FromUserId, COUNT(*) AS ReceivedCount 
    FROM [Conversation] 
    WHERE ToUserId = 'user1'
    GROUP BY FromUserId
) Received ON RU.UserId = Received.FromUserId
WHERE U.UserId <> 'user1' -- 排除user1自己
ORDER BY U.UserId;

测试数据的返回结果 #

两种方案都会返回以下完整记录：

这样就不会再漏掉任何和user1有过消息往来的用户了。

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