mysql - MySQL中如何将多行数据放入一行数组

Question

假设我有一个表days，它指定每天的早餐食物和晚餐食物，使用指向包含食物名称的表food 的ID。如何使用单个查询获取早餐和晚餐食物名称？基本上我想将以下内容合并到一个查询中:

SELECT days.*, food.name AS breakfast_name FROM days, food WHERE days.breakfast_id = food.id AND days.id = '12'
SELECT days.*, food.name AS dinner_name FROM days, food WHERE days.dinner_id = food.id AND days.id = '12'

Answer 1

您可以通过在单独的 ID 上连接食物表两次来在单个查询中完成此操作。

SELECT days.*, bf.name AS breakfast_name, df.name AS dinner_name FROM days JOIN food AS bf ON days.breakfast_id=bf.id JOIN food AS df ON days.dinner_id=df.id;