Group_concat работает некорректно при множественном соединении
У меня проблема с созданием запроса на выборку с данными объединения и конкатенации из 3 таблиц.
Первая сущность таблицы имеет идентификаторы некоторых сущностей с их авторами:
2-я и 3-я таблицы имеют разделы и файлы, относящиеся к этим объектам, по одной строке для каждого. Количество разделов и файлов может быть разным для любой сущности.
файл:
раздел (также некоторые объекты могут быть без разделов, например 12 в этом примере):
Мне нужно выбрать все данные из таблицы сущностей, объединяющей связанные разделы и файлы в виде строк, разделенных запятыми. Для этого я создал следующий запрос:
SELECT
entity.id,
entity.author,
group_concat(section.section_id) section_ids,
group_concat(file.file_name) files
FROM entity
LEFT JOIN file ON entity.id = file.entity_id
LEFT JOIN section ON entity.id = section.entity_id
group by entity.id;
Я ожидаю получить следующий результат:
Но на самом деле я получаю это:
Похоже, что файлы дублируются, если объект имеет несколько разделов, и разделы дублируются, если объект имеет несколько файлов. Я пробовал играть с разными типами соединения (внутреннее/внешнее, правое/левое), но не нашел решения. Пожалуйста, помогите мне исправить этот запрос.
Ответы 2
Проблема заключается в том, что у вас есть несколько совпадений в обеих таблицах для данной сущности: соединения умножают строки, а результаты агрегатов неверны.
Я бы порекомендовал предварительную агрегацию. Пара подзапросов должна отлично справиться с этой задачей:
select e.id, e.author,
(select group_concat(f.file_name) from file f where f.entity_id = e.id) as files_names
(select group_concat(s.section_id) from section s where s.entity_id = e.id) as section_ids
from entity e
Вы объединяете два разных измерения, в результате чего получается декартово произведение. Самая простая корректировка вашего запроса — DISTINCT:
SELECT e.id, e.author,
group_concat(distinct s.section_id) as section_ids,
group_concat(f.file_name) as files
FROM entity e LEFT JOIN
file f
ON e.id = f.entity_id LEFT JOIN
section s
ON e.id = s.entity_id
group by e.id;
Однако для производительности я бы рекомендовал подход, который предлагает GMB.
Я отклонил ваш ответ, потому что он точно такой же, как ответы в дублирующей ссылке, и для того, чтобы опубликовать его, вы открыли закрытый вопрос, хотя знали, что вопрос является дубликатом.
Итак, вы открыли вопрос, который я закрыл как дубликат, и разместили в качестве своего ответа ответ на дубликат ссылки: stackoverflow.com/questions/3083499/…?