mysql - 选择不同的表并对其他表进行计数

Question

在小型聊天服务中，两个用户进行共同聊天。此 SQL 选择与您聊天的用户列表。

SELECT DISTINCT user.user_id, user.username
FROM user
INNER JOIN message
ON user.user_id = message.owner_user_id OR user.user_id = message.to_user_id
WHERE message.owner_user_id = :activeUserId
OR message.to_user_id = :activeUserId
ORDER BY message.date_time DESC

我还需要获取两个用户之间发送的消息数。输出是一个“文件夹”列表，每个文件夹对应一个与事件用户聊天过的用户。每个文件夹包含用户的用户名和聊天中的消息数(活跃用户向特定用户发送的消息数和特定用户向活跃用户发送的消息数之和)。

row1: (active user and JohnSmith have 33 messsages in their common chat.)
    user_id = 1;
    username = 'JohnSmith';
    message_count = 33;

row2: (active user and Johnny have 43 messsages in their common chat.)   
    user_id = 2;
    username = 'Johnny';
    message_count = 43;

如何在一条 SQL 语句中完成此操作？

Answer 1

现在您正在使用DISTINCT“展平”结果。您可以使用 COUNT(*) 函数和 GROUP BY 展平和计数:

SELECT DISTINCT user.user_id, user.username, COUNT(*)
FROM user
INNER JOIN message
  ON user.user_id = message.owner_user_id OR user.user_id = message.to_user_id
WHERE message.owner_user_id = :activeUserId OR message.to_user_id = :activeUserId
GROUP BY user.user_id, user.username
ORDER BY message.date_time DESC

对查询的唯一更改是附加列 (COUNT(*)) 和 GROUP BY 行。