mysql - SQL 查询具有这些成分的食谱)但不是这些成分)

Question

我正在为用户可以查询的食谱网站做一个高级搜索功能；

• 食谱存在某些成分(最多 3 种成分)
• 食谱不存在某些成分(最多 3 种成分)
• 在特定的 cooking 时间下

上述几点可以在用户查询中结合在一起。

我已经完成了一半，我可以查询包含指定成分并在一定时间内烹制的食谱。

这是我的数据库结构；

表:食谱

食谱 ID、食谱名称、 cooking 时间

表:成分

成分_ID，成分_名称

表:Recipe_Ingredients

Recipe_Ingredient_ID, Ingredient_ID, Recipe_ID

到目前为止，这是我的 SQL 查询；

SELECT count(*) as rowcount, r.Recipe_name
FROM Recipes AS r
INNER JOIN Recipe_Ingredients AS ri 
ON r.Recipe_ID = ri.Recipe_ID
INNER JOIN Ingredients AS i
ON ri.Ingredient_ID = i.Ingredient_ID 
AND i.Ingredient_Name IN ('penne','onion')
AND r.Cooking_time < 60
GROUP BY r.Recipe_name HAVING rowcount = 2;

他们将获得包含“通心粉”和“洋葱”的食谱，并在 60 分钟内完成 cooking 。

我想不通的是如何按照以下方式查询食谱；

• 包含“通心粉”和“洋葱”
• 不含“黄油”
• 在“不到 60 分钟”内煮熟

我试过下面的代码，但它不起作用；

SELECT count(*) as rowcount, r.Recipe_name
FROM Recipes AS r
INNER JOIN Recipe_Ingredients AS ri 
ON r.Recipe_ID = ri.Recipe_ID
INNER JOIN Ingredients AS i
ON ri.Ingredient_ID = i.Ingredient_ID 
AND i.Ingredient_Name IN ('penne','onion')
AND i.Ingredient_Name NOT IN ('butter')
AND r.Cooking_time < 60
GROUP BY r.Recipe_name HAVING rowcount = 2;

非常感谢任何帮助!

谢谢。

Answer 1

你可以使用

SELECT r.Recipe_name,
       r.Recipe_ID
FROM   Recipes AS r
       INNER JOIN Recipe_Ingredients AS ri
               ON r.Recipe_ID = ri.Recipe_ID
       INNER JOIN Ingredients AS i
               ON ri.Ingredient_ID = i.Ingredient_ID
WHERE  i.Ingredient_Name IN ( 'penne', 'onion', 'butter' )
       AND r.Cooking_time < 60
GROUP  BY r.Recipe_ID, /*<-- In case two recipes with same name*/
          r.Recipe_name
HAVING
/*Must contain both these*/
COUNT(DISTINCT CASE
                 WHEN i.Ingredient_Name IN ( 'penne', 'onion' ) THEN i.Ingredient_Name
               END) = 2
AND
/*Can't contain these*/
MAX(CASE
      WHEN i.Ingredient_Name IN ( 'butter' ) THEN 1
      ELSE 0
    END) = 0