通过中间表进行SQL查询

Question

给出下表：

Recipes
| id | name
| 1  | 'chocolate cream pie'
| 2  | 'banana cream pie'
| 3  | 'chocolate banana surprise'

Ingredients
| id | name
| 1  | 'banana'
| 2  | 'cream'
| 3  | 'chocolate'

RecipeIngredients
| recipe_id | ingredient_id
|     1     |      2
|     1     |      3
|     2     |      1
|     2     |      2
|     3     |      1
|     3     |      3

如何构建 SQL 查询来查找配料.name = 'chocolate' 和配料.name = 'cream' 的食谱？

Answer 1

使用：

  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

这里的关键点是计数必须等于成分名称的数量。如果不是重复计数，则存在由于重复而导致误报的风险。

Answer 2

这称为关系划分。讨论了各种技术此处。

尚未给出的一种替代方案是双 NOT EXISTS

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))

Answer 3

如果您要搜索多个关联，那么编写查询的最简单方法是使用多个 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

或者，如果一个配方可能有相同成分的多个实例（RecipeIngredients 关联表上没有 UNIQUE 约束），您可以将最后一行替换为：

HAVING COUNT(DISTINCT i.name) = 2

Answer 4

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

Answer 5

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'

Answer 6

另一种方式：

版本 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：（测试结果）；

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

结果的格式为 (First Recipe_id ; Second Recipe_id, Result)

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'