我的数据库中有以下3个表,在查询它们以获得所需结果时遇到了一些麻烦。我正在尝试按成分搜索食谱。
SQL Fiddle的以下模式: fiddle
这是我的桌子:配料
+---------------+---------+ | ingredient_id | name | +---------------+---------+ | 1 | tomato | | 2 | onion | | 3 | rice | | 4 | chicken | | 5 | beef | | 6 | noodles | | 7 | salt | +---------------+---------+
菜谱
+-----------+------------------+ | recipe_id | name | +-----------+------------------+ | 1 | tomato goodness | | 2 | meat deluxe | | 3 | chicken surprise | +-----------+------------------+
成分索引
+-----------+---------------+ | recipe_id | ingredient_id | +-----------+---------------+ | 1 | 1 | | 1 | 5 | | 1 | 7 | | 2 | 5 | | 2 | 6 | | 2 | 7 | | 3 | 4 | | 3 | 3 | | 3 | 7 | +-----------+---------------+
查询仅搜索一种成分的查询效果很好,并输出以下内容:
mysql> select r.recipe_id, r.name -> from recipes r -> inner join ingredient_index -> on i.recipe_id = r.recipe_id -> where -> i.ingredient_id = 7; +-----------+------------------+ | recipe_id | name | +-----------+------------------+ | 1 | tomato goodness | | 2 | meat deluxe | | 3 | chicken surprise | +-----------+------------------+
但是当使用或用于多种成分时,我们得到了
mysql> select r.name -> from recipes r -> inner join ingredient_index i -> on i.recipe_id = r.recipe_id -> where i.ingredient_id = 7 or i.ingredient_id = 5; +------------------+ | name | +------------------+ | tomato goodness | | tomato goodness | | meat deluxe | | meat deluxe | | chicken surprise | +------------------+
设置5行(0.00秒)
并没有使用“和”结果
mysql> select r.name -> from recipes r -> inner join ingredient_index i -> on i.recipe_id = r.recipe_id -> where i.ingredient_id = 7 and i.ingredient_id = 5; Empty set (0.00 sec)
任何帮助将非常感激!
由于配方可以使用多种成分,并且您正在寻找使用指定的一种或多种成分DISTINCT的配方,因此,如果配方使用指定列表中的一种以上成分,则应使用关键字来防止重复结果。另外,您可以使用IN子句来筛选多个成分ID。
DISTINCT
IN
select DISTINCT r.name from recipes r inner join ingredient_index i on i.recipe_id = r.recipe_id where i.ingredient_id IN (7, 5);
或者,如果要查找使用列表中指定的所有成分的配方,则可以按配方名称对结果进行分组,并检查记录数是否与列表中的成分数相同。
select r.name from recipes r inner join ingredient_index i on i.recipe_id = r.recipe_id where i.ingredient_id IN (7, 5) GROUP BY r.name HAVING COUNT(*) = 2
假设不会有重复的记录具有相同的(recipe_id,Ingredient_id)元组(最好通过UNIQUE约束确保)。