通过中间表进行SQL查询

给出以下表格：

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查询以查找recipe.name ='chocolate'和ingredients.name ='cream'的配方？

已邀请:

6 个回复

晤默报

这称为关系分裂。这里讨论了各种技术。尚未给出的另一种选择是双重不存在

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

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

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版，它可以工作。大多数用户在Ingredient表上链接，在这种情况下完全不需要！编辑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'

要回复问题请先登录或注册

通过中间表进行SQL查询

6 个回复

发起人

relational_division

问题状态

通过中间表进行SQL查询

与内容相关的链接

6 个回复

发起人

relational_division

问题状态