通过中间表的SQL查询

这叫做关系除法。讨论了各种技术 here .

一个尚未给出的选择是double不存在

SELECT r.id, r.name
FROM Recipes r
WHERE NOT EXISTS (SELECT * FROM Ingredients i
                  WHERE name IN ('chocolate', 'cream')
                  AND NOT EXISTS
                      (SELECT * FROM RecipeIngredients ri
                       WHERE ri.recipe_id = r.id
                       AND ri.ingredient_id = i.id))

用途:

  SELECT r.name
    FROM RECIPES r
    JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id
    JOIN INGREDIENTS i ON i.id = ri.ingredient_id
                      AND i.name IN ('chocolate', 'cream')
GROUP BY r.name
  HAVING COUNT(DISTINCT i.name) = 2

这里的关键点是计数必须等于成分名称的数量。如果不是一个明显的计数,就有可能由于重复而产生假阳性。

如果要搜索多个关联,则编写查询的最简单方法是使用多个关联 EXISTS 条件而不是单个直线 JOIN .

SELECT r.id, r.name
FROM Recipes r
WHERE EXISTS
(
    SELECT 1
    FROM RecipeIngredients ri
    INNER JOIN Ingredients i
        ON i.id = ri.ingredient_id
    WHERE ri.recipe_id = r.id
    AND i.name = 'chocolate'
)
AND EXISTS
(
    SELECT 1
    FROM RecipeIngredients ri
    INNER JOIN Ingredients i
        ON i.id = ri.ingredient_id
    WHERE ri.recipe_id = r.id
    AND i.name = 'cream'
)

如果您确定关联是唯一的(即一个配方只能有每个成分的一个实例),那么您可以使用带有 COUNT 功能并可能加速(性能取决于DBMS):

SELECT r.id, r.Name
FROM Recipes r
INNER JOIN RecipeIngredients ri
    ON ri.recipe_id = r.id
INNER JOIN Ingredients i
    ON i.id = ri.ingredient_id
WHERE i.name IN ('chocolate', 'cream')
GROUP BY r.id, r.Name
HAVING COUNT(*) = 2

或者,如果一个配方可能有相同成分的多个实例(否 UNIQUE 限制 RecipeIngredients 关联表),可以将最后一行替换为:

HAVING COUNT(DISTINCT i.name) = 2

select r.*
from Recipes r
inner join (
    select ri.recipe_id
    from RecipeIngredients ri 
    inner join Ingredients i on ri.ingredient_id = i.id
    where i.name in ('chocolate', 'cream')
    group by ri.recipe_id
    having count(distinct ri.ingredient_id) = 2
) rm on r.id = rm.recipe_id

SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
    ri.recipe_id = r.id
INNER JOIN Ingredients i ON
    i.id = ri.ingredient_id
WHERE
    i.name IN ( 'cream', 'chocolate' )

编辑以下评论,谢谢!这是正确的方法:

SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
    ri.recipe_id = r.id
INNER JOIN Ingredients i ON
    i.id = ri.ingredient_id AND
    i.name = 'cream'
INNER JOIN Ingredients i2 ON
    i2.id = ri.ingredient_id AND
    i2.name = 'chocolate'

另一种方式:

修订了版本2(作为存储过程)

select   r.name
from   recipes r
where   r.id  = (select  t1.recipe_id
        from  RecipeIngredients t1 inner join
     RecipeIngredients     t2 on t1.recipe_id = t2.recipe_id
     and     t1.ingredient_id = @recipeId1
     and     t2.ingredient_id = @recipeId2)

编辑2: [在人们开始尖叫之前]:

这可以放在版本2的顶部,这将允许按名称查询,而不是传递ID。

select @recipeId1 = recipe_id from Ingredients where name = @Ingredient1
select @recipeId2 = recipe_id from Ingredients where name = @Ingredient2

我已经测试了版本2,它工作正常。大多数用户在配料表上链接,在这种情况下完全不需要!

编辑3:(测试结果);

运行此存储过程时,这些是结果。

结果为格式(第一个配方\u id;第二个配方\u id,结果)

1,1, Failed
1,2, 'banana cream pie'
1,3, 'chocolate banana surprise'
2,1, 'banana cream pie'
2,2, Failed
2,3, 'chocolate cream pie'
3,1, 'chocolate banana surprise'
3,2, 'chocolate cream pie'
3,3, Failed

显然,当两个约束相同时,此查询不处理案例,但适用于所有其他案例。

编辑4:(处理相同的约束案例):

替换此行:

r.id = (select t1...

到

r.id in (select t1...

处理失败的案例:

1,1, 'banana cream pie' and 'chocolate banana surprise'
2,2, 'chocolate cream pie' and 'banana cream pie'
3,3, 'chocolate cream pie' and 'chocolate banana surprise'