将 2 个表左连接到 1 个表上

Question

这一定很简单，但我想不出任何解决方案，也无法在某处找到答案......

我得到了表“用户”
和一张表“博客”（user_id，博客文章）
和一个表“messages”（user_id，message）

我希望得到以下结果：

User | count(blogs) | count(messages)  
Jim | 0 | 3  
Tom | 2 | 3  
Tim | 0 | 1  
Foo | 2 | 0

所以我所做的是：

SELECT u.id, count(b.id), count(m.id) FROM `users` u  
LEFT JOIN blogs b ON b.user_id = u.id  
LEFT JOIN messages m ON m.user_id = u.id  
GROUP BY u.id

它显然不起作用，因为第二个左连接与博客而不是用户相关。有什么建议吗？

Answer 1

首先，如果您只想要计数值，您可以执行子选择：

select u.id, u.name, 
    (select count(b.id) from blogs where userid = u.id) as 'blogs',
    (select count(m.id) from messages where userid = u.id) as 'messages'
from 'users'

请注意，这只是一个普通的 sql 示例，我现在没有 mysql 数据库来测试它。

另一方面，您可以进行联接，但您应该使用外部联接来包含没有博客但有消息的用户。这意味着您会多次获得多个用户，因此分组依据会很有帮助。

Answer 2

如果您在选择中使用聚合函数，SQL 会将所有行折叠成一行。
为了获取超过 1 行，您必须使用 group by 子句。
然后 SQL 将生成每个用户的总计。

最快的选项

SELECT 
  u.id
  , (SELECT(COUNT(*) FROM blogs b WHERE b.user_id = u.id) as blogcount
  , (SELECT(COUNT(*) FROM messages m WHERE m.user_id = u.id) as messagecount
FROM users u   

为什么你的代码不起作用

SELECT u.id, count(b.id), count(m.id) 
FROM users u   
LEFT JOIN blogs b ON b.user_id = u.id       <<-- 3 matches multiplies # of rows *3
LEFT JOIN messages m ON m.user_id = u.id    <<-- 5 matches multiplies # of rows *5
GROUP BY u.id 

计数将会关闭，因为你正在计算重复的项目。

简单修复，但会比选项 1 慢
如果您只计算不同的 id，您将得到正确的计数：

SELECT u.id, count(DISTNICT b.id), count(DISTINCT m.id) 
FROM users u   
LEFT JOIN blogs b ON b.user_id = u.id     
LEFT JOIN messages m ON m.user_id = u.id    
GROUP BY u.id