MySQL - GROUP_CONCAT возвращает повторяющиеся данные, не может использовать DISTINCT

У меня есть нормализованная база данных, и я пытаюсь вернуть данные из нескольких таблиц, используя JOIN и GROUP_CONCAT.

Проблема: строки дублируются с GROUP_CONCAT. Я не могу использовать DISTINCT, потому что некоторые данные (производитель ингредиентов) нужно дублировать.

Вот мой текущий запрос и структура базы данных (SQL Fiddle):

SELECT recipe.*, 
GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
GROUP_CONCAT(ingredient.name) AS iname, 
GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr, 
GROUP_CONCAT(recipe_tag.name) AS tag
FROM  recipe
LEFT JOIN recipe_detail
    ON recipe.id = recipe_detail.recipe_id
LEFT JOIN ingredient
    ON recipe_detail.ingredient_id = ingredient.id
LEFT JOIN ingredient_mfr
    ON ingredient.mfr_id = ingredient_mfr.id
LEFT JOIN recipe_tagmap
    ON recipe.id = recipe_tagmap.recipe_id
LEFT JOIN recipe_tag
    ON recipe_tagmap.tag_id = recipe_tag.id
WHERE recipe.user_id = 1
GROUP BY recipe.id

recipe
+------------+------------+-----------+
|    id      |    name    |  user_id  |
+============+============+===========+
|     1      |  Test123   |     1     |
+------------+------------+-----------+
|     2      |  Test456   |     1     |
+------------+------------+-----------+
|     3      |  Test789   |     1     |
+------------+------------+-----------+

recipe_detail
+------------+---------------+
| recipe_id  | ingredient_id |
+============+===============+
|     1      |      193      |
+------------+---------------+
|     1      |      194      |
+------------+---------------+
|     2      |       16      |
+------------+---------------+
|     3      |      277      |
+------------+---------------+

ingredient
+------------+---------------+---------+
|     id     |      name     |  mfr_id |
+============+===============+=========+
|     16     |       Gin     |    4    |
+------------+---------------+---------+
|     193    |       Fig     |    3    |
+------------+---------------+---------+
|     194    |       Tea     |    3    |
+------------+---------------+---------+
|     277    |       Nut     |    2    |
+------------+---------------+---------+

ingredient_mfr
+------------+------------+
|    id      |    abbr    |
+============+============+
|     2      |    TFA     |
+------------+------------+
|     3      |    FA      |
+------------+------------+
|     4      |    LOR     |
+------------+------------+

recipe_tag
+------------+------------+
|    id      |    name    |
+============+============+
|     1      |    one     |
+------------+------------+
|     2      |    two     |
+------------+------------+
|     3      |    three   |
+------------+------------+
|     4      |    four    |
+------------+------------+
|     5      |    five    |
+------------+------------+
|     6      |    six     |
+------------+------------+
|     7      |    seven   |
+------------+------------+
|     8      |    eight   |
+------------+------------+
|     9      |    nine    |
+------------+------------+

recipe_tagmap
+------------+---------------+---------+
|     id     |   recipe_id   |  tag_id |
+============+===============+=========+
|     1      |       1       |    1    |
+------------+---------------+---------+
|     2      |       1       |    2    |
+------------+---------------+---------+
|     3      |       1       |    3    |
+------------+---------------+---------+
|     4      |       2       |    4    |
+------------+---------------+---------+
|     5      |       2       |    5    |
+------------+---------------+---------+
|     6      |       2       |    6    |
+------------+---------------+---------+
|     7      |       3       |    7    |
+------------+---------------+---------+
|     8      |       3       |    8    |
+------------+---------------+---------+
|     9      |       3       |    9    |
+------------+---------------+---------+

С моим текущим запросом мои результаты выглядят так:

+------+---------+--------------+----------- ----+---------------+------------------+
|  id  |  name   |      iid     |     iname      |    mabbr      |       tag        |
+======+=========+==============+================+===============+==================+
|   1  | Test123 | 193,193,193, | Fig, Fig, Fig, | FA, FA, FA,   | one, two, three, |
|      |         | 194,194,194  | Tea, Tea, Tea  | FA, FA, FA    | one, two, three  |
+------+---------+--------------+----------------+---------------+------------------+
|   2  | Test456 | 16,16,16     | Gin, Gin, Gin  | LOR, LOR, LOR | four, five six   |
+------+---------+--------------+----------------+---------------+------------------+
|   3  | Test789 | 277,277,277  | Nut, Nut, Nut  | TFA, TFA, TFA | seven,eight,nine |
+------+---------+--------------+----------------+---------------+------------------+

Как бы я хотел, чтобы мои результаты выглядели так:

+------+---------+--------------+----------- ----+---------------+------------------+
|  id  |  name   |      iid     |     iname      |    mabbr      |       tag        |
+======+=========+==============+================+===============+==================+
|   1  | Test123 |   193, 194   |    Fig, Tea    |    FA, FA     | one, two, three, |
+------+---------+--------------+----------------+---------------+------------------+
|   2  | Test456 |      16      |      Gin       |     LOR       | four, five six   |
+------+---------+--------------+----------------+---------------+------------------+
|   3  | Test789 |     277      |      Nut       |     TFA       | seven,eight,nine |
+------+---------+--------------+----------------+---------------+------------------+

Как видите, наличие нескольких тегов приводит к дублированию данных об ингредиентах. Наличие нескольких ингредиентов приводит к дублированию тегов. Я пытался использовать DISTINCT, но иногда у меня будет несколько ингредиентов, и каждый из них вернет свой собственный «mabbr», который может быть таким же, как и другой ингредиент (см. первую строку ожидаемых результатов). Используя DISTINCT, он вернет только один экземпляр этого «mabbr».

Есть ли изменение, которое я могу внести в свой запрос, чтобы добиться того, что я хотел бы сделать?

скрипт SQL


mysql sql duplicates left-join group-concat
person Matt Shultz    schedule 03.05.2014    source источник
comment
+1 за хорошо заданный вопрос, показывающий ваши начальные данные, что вы пробовали (э-э, ну, в будущем тяните это и сюда, пожалуйста), проблему, с которой вы столкнулись (и то, что вы знаете, что ее вызывает!) , и какие вы хотите получить результаты. #вздох# Хотел бы я, чтобы больше новых пользователей задавали вопросы, похожие на этот...   -  person Clockwork-Muse    schedule 04.05.2014
comment
Спасибо за комплимент. Я знаю, что вам, ребята, будет легче ответить, если у вас будет как можно больше информации.   -  person Matt Shultz    schedule 04.05.2014

Ответы (2)


arrow_upward
5
arrow_downward

Вы можете решить эту проблему, извлекая группу tag в отдельный подзапрос:

SELECT
    recipe.*,
    GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,
    GROUP_CONCAT(ingredient.name) AS iname,
    GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr,
    (
      SELECT GROUP_CONCAT(recipe_tag.name)
        FROM recipe_tag
          INNER JOIN recipe_tagmap
            ON recipe_tagmap.tag_id = recipe_tag.id
        WHERE recipe_tagmap.recipe_id = recipe.id
     ) AS tag

  FROM recipe
    LEFT JOIN recipe_detail
      ON recipe.id = recipe_detail.recipe_id
    LEFT JOIN ingredient
      ON recipe_detail.ingredient_id = ingredient.id
    LEFT JOIN ingredient_mfr
      ON ingredient.mfr_id = ingredient_mfr.id

  WHERE recipe.user_id = 1
  GROUP BY recipe.id

(пример скрипка)

person transilvlad    schedule 04.05.2014
comment
В вашем примере mabbr все еще дублируется больше раз, чем следовало бы. - person Matt Shultz; 04.05.2014
comment
+1 за рабочий ответ. Я вытащил его из ссылки для вас, потому что в противном случае его следовало бы удалить, тем более что он НЕ соответствовал тому, что сказал ваш ответ. Я обновил JOIN, используемый в подзапросе, чтобы он соответствовал тому, что на самом деле дает вам оптимизатор. Обратите внимание, что для больших наборов результатов может быть более эффективно выполнять (модифицированную версию) подзапрос tag2 в качестве ссылки на таблицу, а не в списке SELECT. - person Clockwork-Muse; 04.05.2014

arrow_upward
-1
arrow_downward

Добавление различных при выполнении GROUP_CONCAT даст вам уникальные значения.

SELECT recipe.*, 
GROUP_CONCAT(distinct recipe_detail.ingredient_id) AS iid,  
GROUP_CONCAT(distinct ingredient.name) AS iname, 
GROUP_CONCAT(distinct ingredient_mfr.abbr) AS mabbr, 
GROUP_CONCAT(distinct recipe_tag.name) AS tag
FROM  recipe
LEFT JOIN recipe_detail
    ON recipe.id = recipe_detail.recipe_id
LEFT JOIN ingredient
    ON recipe_detail.ingredient_id = ingredient.id
LEFT JOIN ingredient_mfr
    ON ingredient.mfr_id = ingredient_mfr.id
LEFT JOIN recipe_tagmap
    ON recipe.id = recipe_tagmap.recipe_id
LEFT JOIN recipe_tag
    ON recipe_tagmap.tag_id = recipe_tag.id
WHERE recipe.user_id = 1
GROUP BY recipe.id

скрипт SQL

person SunilGhargaonkar    schedule 17.04.2019
comment
как сказал OP, он не может использовать DISTINCT, потому что некоторые значения должны быть продублированы - person Andrea Mauro; 20.05.2020