Блог

Схема базы данных

 create table `questions` (
  `id` int not null auto_increment,
  `title` varchar(45) not null,
  primary key (`id`));

create table `tags` (
  `id` int not null auto_increment,
  `question_id` int not null,
  `name` varchar(45) not null,
  primary key (`id`));

create table `comments` (
  `id` int not null auto_increment,
  `question_id` int not null,
  `body` varchar(45) not null,
  primary key (`id`));

insert into questions (title) values
("title1"), ("title2"), ("title3");

insert into tags (question_id, name) values
(1, "javascript"), (1, "php"), (1, "c#"), (2, "mysql"), (2, "php"), (3, "c#");

insert into comments (question_id, body) values
(1, "comment1"), (1, "comment1"), (1, "comment2"), (3, "comment3");
 

Вот как это выглядит визуально:

questions таблица

 | id |  title |
|----|--------|
|  1 | title1 |
|  2 | title2 |
|  3 | title3 |
 

tags таблица

 | id | question_id |       name |
|----|-------------|------------|
|  1 |           1 | javascript |
|  2 |           1 |        php |
|  3 |           1 |         c# |
|  4 |           2 |      mysql |
|  5 |           2 |        php |
|  6 |           3 |         c# |
 

comments таблица

 | id | question_id |     body |
|----|-------------|----------|
|  1 |           1 | comment1 |
|  2 |           1 | comment1 |
|  3 |           1 | comment2 |
|  4 |           3 | comment3 |
 

У каждого вопроса должен быть хотя бы один тег. Он также может иметь 0 или более комментариев. По одному вопросу может быть два комментария с одним и тем же телом.

Желаемый результат

Я хочу выбрать все вопросы, то есть их идентификаторы, заголовки, теги и комментарии.

Вывод должен выглядеть так:

 | id |  title |       tags        |          comments          |
|----|--------|-------------------|----------------------------|
|  1 | title1 | c#,php,javascript | comment1,comment1,comment2 |
|  2 | title2 | php,mysql         | (null)                     |
|  3 | title3 | c#                | comment3                   |
 

Попытки решить проблему

Я попробовал следующий запрос:

 select questions.id, questions.title,
  group_concat(tags.name), group_concat(comments.body)
from questions
join tags on questions.id = tags.question_id
left join comments on questions.id = comments.question_id
group by questions.id
 

К сожалению, это работает не так, как ожидалось. Он выдает следующий вывод:

 | id |  title | group_concat(distinct tags.name) |                                                      group_concat(comments.body) |
|----|--------|----------------------------------|----------------------------------------------------------------------------------|
|  1 | title1 |                c#,php,javascript | comment1,comment1,comment1,comment2,comment2,comment2,comment1,comment1,comment1 |
|  2 | title2 |                        php,mysql |                                                                           (null) |
|  3 | title3 |                               c# |                                                                         comment3 |
 

Как вы видите, для первого вопроса я получаю каждый комментарий три раза, потому что в этом вопросе есть три тега.

Кроме того, комментарии расположены в неправильном порядке. Они должны быть в том же порядке, в котором они были вставлены, то есть, comment1,comment1,comment2 , not comment1,comment2,comment1 .

Я не могу использовать distinct для комментариев, так как по одному вопросу может быть несколько комментариев с одним и тем же телом.

Я знаю, что это, вероятно, можно было бы решить с помощью вложенных select s, но, насколько я знаю, это оказало бы огромное негативное влияние на производительность запроса.

Скрипка SQL

SQL-скрипка со схемой базы данных и моим запросом.

Вам нужно сначала объединить и применить GROUP_CONCAT , а затем присоединиться:

 select questions.id, questions.title,
       tags.name, comments.body
from questions
join (
   select question_id, group_concat(tags.name) as name
   from tags
   group by question_id
) tags on questions.id = tags.question_id
left join (
   select question_id, group_concat(comments.body) as body
   from comments
   group by question_id
) comments on questions.id = comments.question_id
 

Вы можете агрегировать, используя вложенный запрос перед объединением. Поскольку у вас, похоже, есть уникальные теги, то, похоже, вы можете избежать использования подзапроса для тегов и просто присоединиться к ним, как вы делаете в настоящее время:-

 SELECT questions.id, 
        questions.title,
        GROUP_CONCAT(tags.name ORDER BY tags.id), 
        comments.body
FROM questions
LEFT OUTER JOIN tags ON questions.id = tags.question_id
LEFT OUTER JOIN 
(
   SELECT question_id, 
            GROUP_CONCAT(comments.body ORDER BY id) as body
   FROM comments
   GROUP BY question_id
) comments ON questions.id = comments.question_id
GROUP BY questions.id, 
        questions.title,
        comments.body
 

Возможно, вам сойдет с рук коррелированный подзапрос. Это может быть лучше, если у вас есть очень большое количество вопросов, но вы ограничите те, которые вас интересуют, предложением WHERE. Недостатком является то, что я не уверен, что MySQL будет достаточно умен, чтобы выполнять коррелированный подзапрос один раз для каждого вопроса, а не один раз для каждого вхождения вопроса.

 SELECT questions.id, 
        questions.title,
        GROUP_CONCAT(tags.name ORDER BY tags.id), 
        (
            SELECT GROUP_CONCAT(comments.body ORDER BY id) 
            FROM comments
            WHERE questions.id = comments.question_id
            GROUP BY question_id
        ) AS body
FROM questions
LEFT OUTER JOIN tags ON questions.id = tags.question_id
GROUP BY questions.id, 
        questions.title,
        body