我有三张桌子：
食谱：
id | title | ....
配方和配料：
recipe_id | ingredient_id
成分：
id | ingredient
每个食谱都有n种相关的成分。
我写了一个查询，它通过配料id获取配方id、配料匹配计数、该配方的配料总数：
SELECT recipes.id, recipes.title, ing_match_count,
(
SELECT count(id)
FROM recipe_ingredients as ri
WHERE ri.recipe_id = recipes.id
) as recipe_ing_count
FROM recipes
RIGHT JOIN (
SELECT recipe_id, ingredients_id, COUNT(*) AS ing_match_count
FROM recipe_ingredients
WHERE ingredients_id IN (19, 25, 30, 40)
GROUP BY recipe_id
) AS ri
ON recipes.id = ri.recipe_id
ORDER BY ing_match_count DESC
问题是，我无法排除使用其他成分ID的食谱。上面的查询搜索具有19, 25, 30, 40成分ID的食谱。但是我想排除食谱，例如，它也有22,23种成分。
所以，若配方有19和22个成分id，它将不会显示。
我非常喜欢使用groupby和having子句中的条件来处理这些类型的查询。我发现这是最灵活的方法。
SELECT r.id, r.title,
SUM(ingredients_id IN (19, 25, 30, 40)) as ing_match_count,
COUNT(*) as recipe_ing_count
FROM recipes r JOIN
recipe_ingredients ri
ON ri.recipe_id = recipes.id
GROUP BY r.id, r.title
HAVING ing_match_count > 0 AND
SUM(ingredients_id in (22, 23)) = 0;
