鸡尾酒配方数据库：如何为单主键从关联表查询多行并聚合配料为数组

你需要通过表关联+分组聚合来实现需求，不同数据库的聚合函数略有差异，以下是主流数据库的实现方式：

MySQL/SQLite #

SELECT 
    c.id,
    c.name,
    GROUP_CONCAT(i.`ingredient name` SEPARATOR ', ') AS ingredients
FROM cocktail_table c
LEFT JOIN cocktail_ingredients i ON c.id = i.id
GROUP BY c.id, c.name;

PostgreSQL #

SELECT 
    c.id,
    c.name,
    ARRAY_AGG(i."ingredient name") AS ingredients
FROM cocktail_table c
LEFT JOIN cocktail_ingredients i ON c.id = i.id
GROUP BY c.id, c.name;

SQL Server #

SELECT 
    c.id,
    c.name,
    STRING_AGG(i.[ingredient name], ', ') AS ingredients
FROM cocktail_table c
LEFT JOIN cocktail_ingredients i ON c.id = i.id
GROUP BY c.id, c.name;

补充说明 #

• 用LEFT JOIN而非INNER JOIN，确保没有配料的鸡尾酒也会被查询到，此时ingredients字段会返回NULL（或空字符串，依数据库而定）
• 若需去重重复配料，可在聚合函数中添加DISTINCT，例如GROUP_CONCAT(DISTINCT i.ingredient name ...)
• 注意含空格的字段名需用对应数据库的引号包裹：MySQL用反引号``，PostgreSQL用双引号""，SQL Server用方括号[]

内容的提问来源于stack exchange，提问作者ryanmurf9