通过中间表进行 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3029454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query through an intermediate table
提问by Bryan Ash
Given the following tables:
鉴于以下表格：
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
How do I construct a SQL query to find recipes where ingredients.name = 'chocolate' and ingredients.name = 'cream'?
如何构建 SQL 查询以查找配料名称 = '巧克力' 和配料名称 = '奶油' 的食谱？
采纳答案by Martin Smith
This is called relational division. A variety of techniques are discussed here.
这称为关系划分。这里讨论了各种技术。
One alternative not yet given is the double NOT EXISTS
尚未给出的另一种选择是双重 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))
回答by OMG Ponies
Use:
用：
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
The key point here is that the count must equal the number of ingredient names. If it's not a distinct count, there's a risk of false positives due to duplicates.
这里的关键点是计数必须等于成分名称的数量。如果它不是一个独特的计数，则存在由于重复而导致误报的风险。
回答by Aaronaught
If you're searching for multiple associations then the simplest way to write the query is to use multiple EXISTSconditions instead of a single straight JOIN.
如果您要搜索多个关联，那么编写查询的最简单方法是使用多个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'
)
If you know for sure that the associations are unique (i.e. a single recipe can only have a single instance of each ingredient), then you can cheat a bit using a grouping subquery with a COUNTfunction and possibly speed it up (performance will depend on the DBMS):
如果您确定关联是唯一的（即单个配方只能有每个成分的单个实例），那么您可以使用带有COUNT函数的分组子查询来作弊，并可能加快速度（性能将取决于数据库管理系统）：
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
Or, if a recipe might have multiple instances of the same ingredient (no UNIQUEconstraint on the RecipeIngredientsassociation table), you can replace the last line with:
或者，如果一个配方可能有相同成分的多个实例（UNIQUE对RecipeIngredients关联表没有约束），您可以将最后一行替换为：
HAVING COUNT(DISTINCT i.name) = 2
回答by RedFilter
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
回答by Diego Pereyra
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' )
Edited following comment, thanks! This is the right way then:
编辑以下评论，谢谢！这是正确的方法：
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'
回答by Darknight
a different way:
一种不同的方式：
Version 2 (as stored procedure) revised
版本 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)
Edit 2: [before people start screaming] :)
编辑 2: [在人们开始尖叫之前] :)
This can be placed at the top of version 2, which will allow to query by name instead of passing in the id.
这可以放在版本 2 的顶部，这将允许按名称查询而不是传入 id。
select @recipeId1 = recipe_id from Ingredients where name = @Ingredient1
select @recipeId2 = recipe_id from Ingredients where name = @Ingredient2
I've tested version 2, and it works. Most users where linking on the Ingredient table, in this case was totally not needed!
我已经测试了版本 2，它可以工作。大多数用户在成分表上链接，在这种情况下完全不需要！
Edit 3: (test results);
编辑3：（测试结果）；
When this stored procedure is run these are the results.
当这个存储过程运行时，这些是结果。
The results are of the format (First Recipe_id ; Second Recipe_id, Result)
结果的格式为 (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
Clearly this query does not handle case when both constraints are the same, but works for all other cases.
显然，当两个约束相同时，此查询不处理情况，但适用于所有其他情况。
Edit 4:(handling same constraint case):
编辑 4:(处理相同的约束情况）：
replacing this line:
替换这一行：
r.id = (select t1...
to
到
r.id in (select t1...
works with the failed cases to give:
与失败的案例一起工作以提供：
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'
