mysql - Sequel Pro 合并多行，其中只有 1 列不同，但其余部分相同

Question

我有一张饭 table ，并且主 table 上有几个与其他 table 相关联的 ID。

我还有另一张表，其中餐食的成分按 ID 列出，这样我就不必一遍又一遍地输入成分。但是，当我运行以下搜索时，我得到了多次列出的膳食名称，因为每种成分都列在其自己的行上。我怎样才能让它显示名称，然后在同一行显示所有成分？

SELECT
    mealName, ingredient, quantity 
FROM 
    meals, mealTotal, ingredients 
WHERE 
        mealTotal.mealId = meals.MealId 
    and mealTotal.ingredientId =  ingredients.ingredientId  
ORDER BY 
    mealName;

当前显示:

Apple Pie    Apple    1
Apple Pie    Tabantha Wheat    1
Apple Pie    Goat Butter       1
Apple Pie    Cane Sugar    1

我想要它显示什么:

Apple Pie    Apple   1    Tabantha Wheat    1      Goat Butter    1    Cane Sugar    1

Answer 1

您可以使用GROUP_CONCAT以列表形式返回所有成分和数量。因此，从现有的查询开始，您可以将其转换为:

-- Concatenate all your (ingredient, quantity)
SELECT
    mealName, 
    GROUP_CONCAT(ingredient_and_quantity ORDER BY ingredient_and_quantity SEPARATOR '\t' ) AS ingredient_list
FROM
    (SELECT
        mealName, ingredient, quantity, 
        CONCAT(ingredient, ' ', quantity) AS ingredient_and_quantity
    FROM 
        meals
        JOIN mealTotal ON mealTotal.mealId = meals.mealId
        JOIN ingredients ON ingredients.ingredientId = mealTotal.ingredientId 
    ) AS q
ORDER BY 
    mealName;

这将为您提供一个包含所有成分和相应数量的字符串。

mealName  | ingredient_list                                                         :-------- | :-----------------------------------------------------------------------Apple Pie | Apple 1        Cane Sugar 1        Goat Butter 1        Tabantha Wheat 1

Note that I have changed your implicit JOINs to explicit ones. (Implicit JOINs are more difficult to read and error-prone, it's easy you miss one link-condition on the WHERE and you get a carteasian product you weren't expecting).

You can check all this at dbfiddle here

---

If you want to have the list of ingredients as different columns (ingredient_1, quantity_1, ingredient_2, quantity_2, ...), then you can start from a variation of this query, and decompose the grouped data using SUBSTRING_INDEX(str,delim,count):

-- Pivot 
SELECT
    mealName, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  1), ',', -1) AS ingredient_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  2), ',', -1) AS ingredient_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  3), ',', -1) AS ingredient_3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',',  4), ',', -1) AS ingredient_4,

    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  1), ',', -1) AS quantity_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  2), ',', -1) AS quantity_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  3), ',', -1) AS quantity_3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',',  4), ',', -1) AS quantity_4
FROM
    (SELECT
        mealName, 
        GROUP_CONCAT(ingredient ORDER BY ingredient SEPARATOR ',') AS ingredients,
        GROUP_CONCAT(quantity ORDER BY ingredient SEPARATOR ',') AS quantities
    FROM
        (SELECT
            mealName, ingredient, quantity
        FROM 
            meals
            JOIN mealTotal ON mealTotal.mealId = meals.mealId
            JOIN ingredients ON ingredients.ingredientId = mealTotal.ingredientId 
        ORDER BY
            mealName, ingredient
        ) AS q
    ) AS q2
ORDER BY 
    mealName;

这会给你类似的东西:

mealName  | ingredient_1 | ingredient_2 | ingredient_3 | ingredient_4   | quantity_1 | quantity_2 | quantity_3 | quantity_4:-------- | :----------- | :----------- | :----------- | :------------- | :--------- | :--------- | :--------- | :---------Apple Pie | Apple        | Cane Sugar   | Goat Butter  | Tabantha Wheat | 1.01       | 1.04       | 1.03       | 1.02      

您可以在 dbfiddle here 处查看此内容

请注意，这不会缩放(如果您需要 10 列，则必须手动完成)，并且由于 STRING_INDEX 的工作方式，某些值可能会重复。我不推荐这种方法。