在一个表上左连接两个表

It must be pretty easy, but i can't think of any solution nor can I find an answer somewhere...

这一定很容易，但我想不出任何解决办法，我也找不到答案……

I got the table 'users'
and one table 'blogs' (user_id, blogpost)
and one table 'messages' (user_id, message)

我得到了表“users”和表“blogs”(user_id, blogpost)以及表“messages”(user_id, message)

I'd like to have the following result:

我想要得到以下结果:

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

So what I did is:

我所做的是:

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

It obviously doesn't work, because the second left join relates to blogs not users. Any suggestions?

显然它不起作用，因为第二个左连接关系到blog而不是用户。有什么建议吗?

2 个解决方案

#1

First, if you only want the count value, you could do subselects:

首先，如果你只想要count值，你可以做subselect:

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'

Note that this is just a plain sql example, I have no mysql db here to test it right now.

注意，这只是一个普通的sql示例，这里没有mysql db来测试它。

On the other hand, you could do a join, but you should use an outer join to include users without blogs but with messages. That would imply that you get several users multiple times, so a group by would be helpful.

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

#2

If you use an aggregate function in a select, SQL will collapse all your rows into a single row.
In order to get more than 1 row out you must use a group by clause.
Then SQL will generate totals per user.

如果在select中使用聚合函数，那么SQL将把所有行折叠成一行。为了得到多于一行，你必须使用group by子句。然后SQL将生成每个用户的总数。

Fastest option

最快的选项

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

Why you code does not work

为什么你的代码不能工作

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

The count will be off, because you are counting duplicate items.

计数将被关闭，因为您正在计数重复的项目。

Simple fix, but will be slower than option 1
If you only count distinct id's, you will get the correct counts:

简单的修正，但将比选项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

#1