mysql - 显示 GROUP 成员的 COUNT(未实际进行 GROUPing)

Question

我想显示用户自上次在线事件以来的新消息。这是我当前的查询:

SELECT 
    from_user_id, 
    to_user_id, 
    from_user_id + to_user_id AS combined_key, 
    chat_date, 
    last_activity, 
    message, 
    username, 
    city
FROM chat AS c JOIN users AS u ON (c.from_user_id = u.id)
WHERE (from_user_id = '4' OR to_user_id = '4') AND chat_date > '2014-06-19 00:00:00.0'
ORDER BY combined_key ASC, chat_date ASC

...产生以下结果:

现在我想添加一列，该列在每行中显示具有相同combined_key 的消息数，因此此小计列将例如:前三行显示 3(因为有 3 条消息的 combined_key '9')。

所以类似

SELECT 
    COUNT(*) AS subtotal, 
    from_user_id, 
    to_user_id, 
    from_user_id + to_user_id AS combined_key, 
    chat_date, 
    last_activity, 
    message, 
    username, 
    city
FROM chat AS c JOIN users AS u ON (c.from_user_id = u.id)
WHERE (from_user_id = '4' OR to_user_id = '4') AND chat_date > '2014-06-19 00:00:00.0'
GROUP BY combined_key

...但没有实际分组并因此消除了一些行:

Answer 1

您所需要做的就是将子选择添加到选择语句中。在sql中，你可以在任何地方添加子选择。我们将返回数据，而不是在子查询中比较信息或创建表。您可能需要编辑此子选择才能使用您的规范(例如，您可能想要指定聊天日期，如在 where 子句中)。此外，根据联接如何影响返回的行，您可能还需要在子选择中包含联接。您必须尝试一些事情才能看到!使用您的原始查询:

SELECT (Select count(1)
          from chat c2
          where c2.combined_key = c.combined_key
          Group by c2.combined_key) as subtotal,
      from_user_id,
      to_user_id,
      from_user_id + to_user_id AS combined_key,
      chat_date,
      last_activity,
      message,
      username,
      city
 FROM chat AS c JOIN users AS u ON (c.from_user_id = u.id)
 WHERE (from_user_id = '4' OR to_user_id = '4') AND chat_date > '2014-06-19 00:00:00.0'
 ORDER BY combined_key ASC, chat_date ASC