SQL-запрос, который объединяет две таблицы и возвращает несколько совпадений из одного столбца?

Мой заголовок ужасен, и, вероятно, поэтому я не могу найти то, что хочу, в Google.

Что я пытаюсь сделать, так это экспортировать некоторые данные из старого внутреннего блога, чтобы я мог импортировать их во что-то еще. Моя проблема в том, что, хотя я могу создать вид JOIN, который я ищу, совпадение во второй таблице может содержать несколько строк, поэтому я получаю тонны повторяющихся данных. Мне нужно взять результаты из второй таблицы и объединить их (если совпадений несколько) в одно поле в результате запроса. Нет необходимости в ограничении WHERE для запроса, я пытаюсь получить всю таблицу blog_posts.

Надеюсь, эта сокращенная схема структуры таблицы поможет проиллюстрировать:

blog_posts              blog_categories
---------------------------------------
post_id                 post_id
post_content            category_id
post_author

А вот и примерные данные.

Данные таблицы blog_posts:

post_id  post_content  post_author
----------------------------------
1        foo1          bob
2        foo2          bob
3        foo3          fred

Данные таблицы blog_categories:

post_id  category_id
--------------------
1        1
1        2
1        6
2        1
3        2
3        4

Мои идеальные результаты выглядели бы так:

post_id  post_content  post_author  category_ids
------------------------------------------------
1        foo1          bob          1,2,6
2        foo2          bob          1
3        foo3          fred         2,4

Самое близкое, что я мог получить, было простое соединение, подобное этому:

SELECT 
    blog_posts.post_id, 
    blog_posts.post_content, 
    blog_posts.post_author, 
    blog_categories.category_id 
FROM blog_posts 
    INNER JOIN blog_categories 
        ON blog_posts.post_id = blog_categories.post_id

Но это возвращает совпадения в таблице blog_posts несколько раз (по одному разу для каждого совпадающего идентификатора category_id).

Есть ли способ выполнить то, что я хочу, используя только SQL? Я думаю, что какой-то подвыбор будет работать, но я не могу понять, как это будет работать - я знаю, что, по сути, хотел бы сделать выбор в моем "цикле" для идентификаторов категорий, используя текущий идентификатор сообщения, но синтаксис для этого ускользает от меня. Это не должно быть эффективным, это одноразовая операция.


mysql sql join subquery concat
person sporker    schedule 04.03.2013    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Функция group_concat() точно выполняет что вам нужно:

SELECT 
  blog_posts.post_id, 
  blog_posts.post_content, 
  blog_posts.post_author, 
  group_concat(blog_categories.category_id)
FROM blog_posts 
JOIN blog_categories ON blog_posts.post_id = blog_categories.post_id
GROUP BY 1, 2, 3
person Bohemian♦    schedule 04.03.2013
comment
@BenjaminM YEs - это только функция mysql, но вопрос в mysql - person Bohemian♦; 04.03.2013
comment
вопрос в mysql, так как я сделал это mysql;) - person Benjamin M; 04.03.2013
comment
Я смотрю на это, и хотя вывод BLOB вызывает у меня проблемы с экспортом, похоже, он работает. Я вообще не понимаю, как это работает, особенно GROUP BY в конце. Это category_id или post_id? У меня около 420 строк post_id и 40 строк category_id — я надеюсь, что моей GROUP BY на самом деле не нужно перечислять каждую из них. - person sporker; 04.03.2013
comment
Кроме того, если я использую GROUP BY blog_posts.post_id, blog_posts.post_content, blog_posts.post_author в конце запроса, это, безусловно, работает. Я выборочно проверил некоторые данные, и они выглядят хорошо. Даже добавил еще один JOIN, чтобы я мог вытащить названия категорий из еще одной таблицы. - person sporker; 04.03.2013
comment
@sporker стандарт SQL позволяет ссылаться на сгруппированные столбцы по их позиции, а не по их выражению. Это особенно удобно, когда в колонке используется длинный расчет, но я нахожу краткость приятной. Done избегает этого синтаксиса, но я принимаю его. - person Bohemian♦; 04.03.2013

arrow_upward
0
arrow_downward

Вы хотите GROUP BY blog_posts.post_id, blog_posts.post_content, blog_posts.post_author. Затем используйте aggregate function (http://en.wikipedia.org/wiki/Aggregate_function), чтобы возьмите ВСЕ значения blog_categories.category_id из каждой группы и превратите их в одну строку.

Какую СУБД вы используете? Для Postgres вы, вероятно, могли бы просто использовать массив в качестве агрегатной функции:

SELECT
  blog_posts.post_id,
  blog_posts.post_content,
  blog_posts.post_author,
  ARRAY_AGG(blog_categories.category_id)
FROM blog_posts
INNER JOIN blog_categories ON blog_posts.post_id = blog_categories.post_id
GROUP BY
  blog_posts.post_id,
  blog_posts.post_content,
  blog_posts.post_author

Или используйте ARRAY_TO_STRING(ARRAY_AGG(blog_categories.category_id), ','), чтобы получить строку, разделенную запятыми.

person Benjamin M    schedule 04.03.2013
comment
Просто для протокола, это MySQL. - person sporker; 04.03.2013
comment
Извиняюсь. С другой стороны, я чаще работаю с PostgreSQL, чем с MySQL, поэтому я добавил ваш ответ в свой локальный список заметок. - person sporker; 04.03.2013