Left Join 2 стола на 1 столе

Это должно быть довольно легко, но я не могу придумать никакого решения и не могу найти ответ где-нибудь...

У меня есть таблица "пользователи"
и одна таблица "блоги" (user_id, сообщение в блоге)
и одна таблица "сообщения" (user_id, сообщение)

Я хотел бы иметь следующий результат:

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

Это явно не работает, потому что второе левое соединение относится к блогам, а не к пользователям. Какие-либо предложения?


mysql left-join multiple-tables
person user993692    schedule 13.10.2011    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Во-первых, если вам нужно только значение счетчика, вы можете сделать подзапросы:

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, чтобы протестировать ее прямо сейчас.

С другой стороны, вы можете присоединиться, но вам следует использовать outer join для включения пользователей без блогов, но с сообщениями. Это будет означать, что вы получаете нескольких пользователей несколько раз, поэтому группировка по будет полезна.

person z00l    schedule 13.10.2011
comment
@ user993692 - Это зависит от подзапроса. Современные базы данных достаточно умны, чтобы оптимизировать подвыборку, и в этом случае вам нужен только поиск по ключу, который довольно легковесен (при условии, что у вас нет квадриллиона пользователей). - person z00l; 13.10.2011

arrow_upward
1
arrow_downward

Если вы используете агрегатную функцию в выборе, SQL свернет все ваши строки в одну строку.
Чтобы получить более одной строки, вы должны использовать предложение 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
Если вы подсчитываете только отдельные идентификаторы, вы получите правильные подсчеты:

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
person Johan    schedule 13.10.2011
comment
Я уже получил group by, просто забыл его записать. Но результат все равно неверный, будет выглядеть так: User | count(blogs) | count(messages) Jim | 0 | 0 Tom | 2 | 2 Tim | 0 | 0 Foo | 2 | 2 - person user993692; 13.10.2011