MySQL - GROUP_CONCAT 返回重複數據，不能使用 DISTINCT

我有一個規範化的數據庫，我正在嘗試使用 JOIN 和 GROUP_CONCAT 從多個表中返回數據。

問題：行與 GROUP_CONCAT 重複。我不能使用 DISTINCT，因為某些數據（成分 mfr）確實需要複製。

這是我目前的查詢和數據庫結構（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小提琴

您已經確定了問題的根源：recipe連接到兩個表，recipe_detail並且recipe_tagmap（這些連接到與“成分”和“標籤”分別相關的其他幾個表），並且recipe與它們具有一對多的關係。

一種解決方案是先單獨GROUP BY聚合（一個聚合用於與成分相關的表列表，另一個聚合用於與標籤相關的表組，然後（再次）連接回主表 ( recipe)：

SELECT recipe.*, 
      iid,  
      iname, 
      mabbr, 
      tag
FROM  recipe

 LEFT JOIN 
   ( SELECT recipe_detail.recipe_id,
            GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
            GROUP_CONCAT(ingredient.name) AS iname, 
            GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr
     FROM recipe
       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_detail.recipe_id
   ) AS details
       ON recipe.id = details.recipe_id

 LEFT JOIN
   ( SELECT recipe_tagmap.recipe_id,
            GROUP_CONCAT(recipe_tag.name) AS tag 
     FROM recipe
       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_tagmap.recipe_id
   ) AS tags
     ON recipe.id = tags.recipe_id

WHERE recipe.user_id = 1 ;

測試於：SQL-Fiddle

（使用recipe2 個聚合中的表並不是嚴格需要的，但由於您只需要一個使用者的配方，這將有助於提高效率，限制從多個表中檢索並聚合的行數。）

引用自：https://dba.stackexchange.com/questions/64438