SQL查询需要查找可能使用IN和NOT IN的不同ID [英] SQL-Query needed to find distinct IDs probably using IN and NOT IN

问题描述

假装我有一个庞大的配方数据库。一张带有唯一ID的配方表和一张带有很多组合的配料表：

  ID |配方|成分
 ------------------------------- 
 1 | recipe_a | Ingredient_a 
 2 | recipe_a | Ingredient_b 
 3 | recipe_a | Ingredient_c 
 4 | recipe_b | Ingredient_a 
 5 | recipe_b | Ingredient_d 
 6 | recipe_b | Ingredient_e 
  

用户可以搜索自己想在食谱中看到的成分和不需要的成分。当用户搜索带有Ingredient_a和Ingredient_b而不是Ingredient_d的食谱时，所需的查询应该能够产生recipe_a。

在一个查询中最好怎么做？

我尝试了比较幼稚的版本：

 选择不同的配方
来自成分
，其中成分（ingredient_a，Ingredient_b）
和成分不在（ingredient_d）
  

此obv失败了，因为它仍然会导致应做的recipe_a和recipe_b，因为第1行和第2行与配方_a相匹配，第4行与配方_b相匹配。 = h2_lin>解决方案

 从配方中选择$ ... 
作为R 
其中R.ingredient in（ingredient_a，Ingredient_b。 ..）
但不存在（
从配方中选择1 
作为R2 
其中R2.Recipe = R.Recipe 
和R2.Ingredient In（ingr edient_d）
）
  

---

如Jeffrey L惠特里奇提到，上面的查询将返回任何在期望列表中具有至少一种成分且在不期望列表中没有任何成分的配方。但是，如果您要返回包含 all 所需列表中所有成分的菜谱，而又不包含不想要的列表中的成分，则可以执行以下操作：

 选择不同的... 
从R的食谱
存在（
从R 2的食谱中选择1 
作为R2 
在R2.Recipe = R.Recipe 
和R2.inredient in（ingredient_a，Ingredient_b ...）
具有Count（*）= @CountOfPassedIngredients 
）
不存在（
从配方中选择1 
作为R2 
其中R2.Recipe = R.Recipe 
和R2.Ingredient In（ingredient_d）
）
  

在这种情况下，您需要首先确定所需成分的数量。

Let's pretend I have a large recipe-database. A table for recipes each with an unique ID and a table for ingredients with a whole lot of sets like this:

ID | RECIPE    |  INGREDIENT
-------------------------------
1  | recipe_a  |  ingredient_a
2  | recipe_a  |  ingredient_b
3  | recipe_a  |  ingredient_c
4  | recipe_b  |  ingredient_a
5  | recipe_b  |  ingredient_d
6  | recipe_b  |  ingredient_e

Users can search for ingredients they want to see in their recipes and those they don't. The wanted query should be able to result in recipe_a when a user searches for recipes with ingredient_a and ingredient_b but not ingredient_d.

How would one do that in preferrably one query?

I tried the rather naive version with:

SELECT distinct recipe 
  from ingredients 
 where ingredient in (ingredient_a, ingredient_b) 
   and ingredient not in (ingredient_d)

This obv failed, because it still resulted in recipe_a and recipe_b, which it should do, because the rows 1 and 2 matched recipe_a and row 4 matched recipe_b.

解决方案

Select Distinct ...
From Recipes As R
Where R.ingredient in(ingredient_a, ingredient_b...)
    And Not Exists(
                    Select 1
                    From Recipes As R2
                    Where R2.Recipe = R.Recipe
                        And R2.Ingredient In(ingredient_d)
                    )

---

As Jeffrey L Whitledge mentioned, the above query will return any recipe that has at least one ingredient in the desired list and none in the undesired list. However, if you wanted to return recipes that contained all the ingredients in the desired list and none in the undesired list you could do:

Select Distinct ...
From Recipes As R
Where Exists    (
                Select 1
                From Recipes As R2
                Where R2.Recipe = R.Recipe
                    And R2.ingredient in(ingredient_a, ingredient_b...)
                Having Count(*) = @CountOfPassedIngredients
                )
    And Not Exists(
                    Select 1
                    From Recipes As R2
                    Where R2.Recipe = R.Recipe
                        And R2.Ingredient In(ingredient_d)
                    )

In this scenario, you would need to have first determine the count of desired ingredients.

这篇关于SQL查询需要查找可能使用IN和NOT IN的不同ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！