Левое соединение 2 таблиц в 1 таблице

#mysql #left-join #multiple-tables

#mysql #левое соединение #несколько таблиц

Вопрос:

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

Я получил таблицу «пользователи»
и одну таблицу «блоги» (user_id, blogpost)
и одну таблицу «сообщения» (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

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

Ответ №1:

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

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

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

1. @user993692 — Это зависит от подзапроса. Современные базы данных достаточно умны, чтобы оптимизировать подвыборку, и в этом случае вам нужен только поиск по ключу, который довольно легкий (предполагается, что у вас нет квадриллиона пользователей).

Ответ №2:

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

 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