mysql - 计算子查询中匹配的数量以对查询进行排序

Question

我正在尝试计算子查询中匹配的数量，以对结果进行排序。

我正在尝试的查询:

SELECT recipes.*, (SELECT COUNT(ingredient_id) FROM recipes WHERE ingredient_id IN(1,3))            AS ingredient_matches 
FROM recipes 
INNER JOIN ingredients 
ON recipes.id = ingredients.recipe_id 
INNER JOIN ingredients_available 
ON ingredients.ingredient_id = ingredients_available.id
GROUP BY recipes.id 
ORDER BY ingredient_matches DESC;

数据库结构:

recipes:
id, name
ingredients:
id, recipe_id, ingredient_id
ingredients_available:
id, name
tags:
id, recipe_id, tag_id
tags_available:
id, name

下面的查询有效，但我希望能够访问不匹配的成分，这样我可以说他们需要它们，如果这有意义的话？

SELECT recipes.*, COUNT(ingredients.id) AS ingredient_matches 
FROM recipes 
INNER JOIN ingredients 
ON recipes.id = ingredients.recipe_id 
INNER JOIN ingredients_available 
ON ingredients.ingredient_id = ingredients_available.id
WHERE ingredient_id IN (1, 5) 
GROUP BY recipes.id 
ORDER BY ingredient_matches DESC;

Answer 1

http://sqlfiddle.com/#!2/3f9ef/15

这是想要的结果。这是因为我没有确保子查询中的recipe_id = Members.recipe_id。