如何在左连接中返回多行 - How to return multiple rows in a LEFT JOIN

我有一个情况，假设我试图获取有关某些食物的信息。然后我需要显示所有信息以及该食物中的所有成分。

通过我的查询，我正在获取数组中的所有信息，但只有第一个成分......

   myFoodsArr = 
        [0]
          foodDescription = "the description text will be here"
          ratingAverage = 0
          foodId = 4
          ingredient = 1
          ingAmount = 2
          foodName = "Awesome Food name"
          typeOfFood = 6
          votes = 0

我想得到这样的东西...

        myFoodsArr = 
            [0]
              foodDescription = "the description text will be here"
              ratingAverage = 0
              foodId = 4
              ingArr = {ingredient: 1, ingAmount: 4}, {ingredient: 3, ingAmount: 2}, {ingredient: 5, ingAmount: 1}
              foodName = "Awesome Food name"
              typeOfFood = 6
              votes = 0

这是我现在使用的查询。如何调整它以返回食物 ID 4，然后获取该食物的所有成分？同时做其他事情，比如获得该食物的平均评分？

谢谢！

   SELECT a.foodId, a.foodName, a.foodDescription, a.typeOfFood, c.ingredient, c.ingAmount, AVG(b.foodRating) AS ratingAverage, COUNT(b.foodId) as tvotes 
FROM `foods` a 
LEFT JOIN `foods_ratings` b 
   ON a.foodId = b.foodId 
LEFT JOIN `foods_ing` c 
   ON a.foodId=c.foodId 
WHERE a.foodId=4

编辑：

Catcall 引入了我从未听说过的"子查询"概念，所以我正在尝试完成这项工作，看看我是否可以在 1 个查询中轻松完成此操作。但我只是不断得到一个错误的回报。这就是我尝试的，但没有运气。

//I changed some of the column names to help them be more distinct in this example
SELECT a.foodId, a.foodName, a.foodDescription, a.typeOfFood, AVG(b.foodRating) AS ratingAverage, COUNT(b.foodId) as tvotes 
FROM foods a 
LEFT JOIN foods_ratings b ON a.foodId = b.foodId 
LEFT JOIN (SELECT fId, ingredientId, ingAmount 
                 FROM foods_ing 
                 WHERE fId = 4 
                 GROUP BY fId) c ON a.foodId = c.fId 
           WHERE a.foodId = 4";

编辑 1 与罗兰兹GROUP_CONCAT/JSON 想法相关的更多事情作为解决方案 4 这个

我正在尝试确保 im 发送回我的 Flash 项目的 JSON 字符串已准备好正确解析Invalid JSON parse input.不断弹出。

所以我认为我需要将所有双引号正确放在正确的位置。

但是在我的 MySQL 查询字符串中，我试图转义双引号，但随后它使我的 mySQL 变量不起作用，例如......

如果我这样做..

GROUP_CONCAT('{'"ingredient'":', '"c.ingredient'", ','"ingAmount'":', '"c.ingAmount'", '}')`

我明白了...

{"ingredient":c.ingredient,"ingAmount":c.ingAmount},{"ingredient":c.ingredient,"ingAmount":c.ingAmount},{"ingredient":c.ingredient,"ingAmount":c.ingAmount}

如何使用所有双引号使 JSON 正确形成而不会破坏 mysql？

这应该可以解决问题：

SELECT    food_ingredients.foodId
,         food_ingredients.foodName
,         food_ingredients.foodDescription
,         food_ingredients.typeOfFood
,         food_ingredients.ingredients
,         AVG(food_ratings.food_rating) food_rating
,         COUNT(food_ratings.foodId)    number_of_votes 
FROM      (
           SELECT    a.foodId
           ,         a.foodName
           ,         a.foodDescription
           ,         a.typeOfFood
           ,         GROUP_CONCAT(
                         '{ingredient:', c.ingredient,
                     ,   ',ingAmount:', c.ingAmount, '}'
                     ) ingredients
           FROM      foods a 
           LEFT JOIN foods_ing c 
           ON        a.foodsId = c.foodsId 
           WHERE     a.foodsId=4
           GROUP BY  a.foodId
          ) food_ingredients
LEFT JOIN food_ratings
ON        food_ingredients.foodId = food_ratings.foodId
GROUP BY  food_ingredients.foodId

请注意，在任何基于 SQL 的数据库中，您要执行的查询类型都不是微不足道的。

主要问题是你有一个主人(食物(有两个细节(成分和评级(。因为这些细节彼此之间(除了与主人之外(无关，所以它们彼此形成了笛卡尔乘积(仅受它们与主人的关系约束(。

上面的查询通过 2 个步骤来解决这个问题：首先，联接到第一个细节(成分(并聚合详细信息(使用 group_concat 将所有相关成分行组成一行(，然后将该结果联接到第二个细节(评级(并再次聚合。

在上面的示例中，成分以结构化字符串返回，与示例中显示的完全相同。如果你想访问 PHP 内部的数据，你可以考虑添加更多的语法，使其成为一个有效的 JSON 字符串，这样你就可以使用 php 函数 json_decode() ： http://www.php.net/manual/en/function.json-decode.php 将其解码为数组

为此，只需将行更改为：

CONCAT(
    '['
,   GROUP_CONCAT(
        '{"ingredient":', c.ingredient
    ,   ',"ingAmount":', c.ingAmount, '}'
    )
,   ']'
)

(这假设ingredient和ingAmount是数字;如果它们是字符串，则应将它们双引号括起来，并转义字符串值中出现的任何双引号(

如果保留 group_concat_max_len 服务器变量的默认设置，则成分与GROUP_CONCAT的串联可能会导致问题。缓解该问题的一个简单方法是将其设置为任何结果的最大理论大小：

SET group_concat_max_len = @@max_allowed_packet;

您可以在打开与 mysql 的连接后执行一次，然后它将在该会话期间生效。或者，如果您具有超级权限，则可以全面更改整个MySQL实例的值：

SET GLOBAL group_concat_max_len = @@max_allowed_packet;

您还可以在 my.cnf 或 my.ini 中添加一行，以group_concat_max_lenght设置为某个足够大的任意静态值。见 http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_group_concat_max_len

一个明显的解决方案是实际执行两个查询：

1(获取食物

SELECT a.foodId, a.foodName, a.foodDescription, a.typeOfFood
FROM `foods` a
WHERE a.foodsId=4

2(获取其所有成分

SELECT c.ingredient, c.ingAmount
FROM `foods_ing` c
WHERE c.foodsId=4

这种方法的优点是不会将"食物"表中的数据复制到结果中。缺点是您必须执行两个查询。实际上，您必须为每个"食物"执行一个额外的查询，因此，如果您想列出包含所有成分的食物，则必须对每个食物记录进行查询。

其他解决方案通常有很多缺点，其中之一是使用 GROUP_CONCAT 函数，但它对返回字符串的长度有严格的限制。

当你将MySQL的聚合函数和GROUP BY行为与SQL标准进行比较时，你必须得出结论，它们只是被破坏了。您可以在单个查询中执行所需的操作，但需要联接返回聚合函数结果的查询，而不是直接联接到评级表。沿着这些思路的东西应该有效。

select a.foodId, a.foodName, a.foodDescription, a.typeOfFood, 
       c.ingredient, c.ingAmount,
       b.numRatings, b.avgRating
from foods a
left join (select foodId, count(foodId) numRatings, avg(foodRating) avgRating
           from foods_ratings
           group by foodId) b on a.foodId = b.foodId
left join foods_ing c on a.foodId = c.foodId
order by a.foodId