MySQL分组重复ID而不丢失其他数据

Question

我正在尝试制作一本食谱，但遇到了重复问题。通过一个例子会更容易理解，所以让我们开始:

我的表格如下所示:

create table Recipe (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(25), 
    description VARCHAR(50), 
    instructions VARCHAR(500)) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table Ingredient (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(50)) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8; 

create table Measure (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(30)) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8; 

create table RecipeIngredient (
    recipe_id INT NOT NULL, 
    ingredient_id INT NOT NULL, 
    measure_id INT, 
    amount INT, 
    CONSTRAINT fk_recipe FOREIGN KEY(recipe_id) REFERENCES Recipe(id), 
    CONSTRAINT fk_ingredient FOREIGN KEY(ingredient_id) REFERENCES Ingredient(id), 
    CONSTRAINT fk_measure FOREIGN KEY(measure_id) REFERENCES Measure(id)) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8; 

当我运行如下所示的查询时:

SELECT r.name AS 'Recipe', 
    r.instructions, 
    ri.amount AS 'Amount', 
    mu.name AS 'Unit of Measure', 
    i.name AS 'Ingredient' 
FROM Recipe r 
JOIN RecipeIngredient ri on r.id = ri.recipe_id 
JOIN Ingredient i on i.id = ri.ingredient_id 
LEFT OUTER JOIN Measure mu on mu.id = measure_id;

我得到这个结果:

问题是巧克力蛋糕是重复的，即使它是相同的食谱，只是有不止一种成分。您能帮我解决这个问题吗，这样我就可以获得一个 Recipe 实例？

Answer 1

使用GROUP_CONCAT将组中多行的值合并到一个以逗号分隔的列表中。并使用 CONCAT 连接字符串，例如将数量连接到单位和成分。

SELECT r.name, r.instructions,
    GROUP_CONCAT(CONCAT(ri.amount, mu.name, ' ', i.name)) AS ingredients
FROM Recipe r 
JOIN RecipeIngredient ri on r.id = ri.recipe_id 
JOIN Ingredient i on i.id = ri.ingredient_id 
LEFT OUTER JOIN Measure mu on mu.id = measure_id
GROUP BY r.id;