php - 给定SELECT语句，我想要多个子查询的结果RECIPE_ID吗？

我有ingredient。ingredientId是709,710,711，我将这个id放在成分Sub-Query中，并且AS match_percentage我很好(3*100 / count计数着ingredient。ingredientId，这全部来自我的PHP代码我为我的解决方案给出这个例子。

现在结果像那些我通过的三个RECIPE_ID。ingredient与此表ingredientId。recipe_ingredient匹配并保留ingredientId。recipe_ingredient

我要recipeId |结果为1和3。只有WHO拥有三个ID 709,710,711，甚至没有两个709,710。

这是我的RECIPE_ID查询：

SELECT
    `recipe`.`recipeId` AS recipe_id ,
    (select count(`recipe_ingredient`.ingredientId) from `recipe_ingredient` where `recipe`.recipeId = `recipe_ingredient`.recipeId) as ingredientCount ,
    IF(
        (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId)>99
        ,100
        ,round( (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId) )
    )
    as match_percentage ,
    GROUP_CONCAT(
        DISTINCT `recipe_ingredient`.ingredientId
        ORDER BY `recipe_ingredient`.ingredientId ASC
           ) as recipeIngredients
from `recipe`
left join `recipe_ingredient` on `recipe_ingredient`.recipeId = `recipe`.recipeId
left join `ingredient` on `ingredient`.ingredientId = `recipe_ingredient`.ingredientId
where  `recipe`.`recipeId` IN(
                SELECT
                `recipe_ingredient`.`recipeId`
                FROM `recipe_ingredient`
                WHERE `recipe_ingredient`.`ingredientId`
                IN(
                  SELECT `ingredient`.`ingredientId` AS linkIng
                  FROM `ingredient`
                  WHERE `ingredient`.`ingredientId` IN(709,710,711) or `ingredient`.`linkIngredientPerent` IN(709,710,711)
                )
                GROUP BY `recipe_ingredient`.`recipeId`
                ORDER BY `recipe_ingredient`.`recipeId` ASC
              )

and (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId) > 24
group by `recipe`.recipeId

我的查询链接：http://sqlfiddle.com/#!2/f4983/2

最佳答案

这是你想要的吗？请在其他情况下进行检查。

http://sqlfiddle.com/#!2/f4983/8