我有ingredient
.ingredientId
是 709,710,711 这个 ID 我被放入成分子查询中,AS match_percentage
我是赖特(3*100 / count
正在计算ingredient
。ingredientId
,这一切都来自我的PHP代码,在这里我为我的解决方案提供了这个例子。
现在结果像那些RECIPE_ID
我通过了三个ingredient
.ingredientId
与此表recipe_ingredient
匹配。ingredientId
并保持recipe_ingredient
.recipeId
我想要结果上的 RECIPE_ID
| 1 和 3.只有谁有那三个ids 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