如何编写SQL查询语句筛选出包含指定全部食材的菜品

编写SQL查询返回至少包含指定全部食材的菜品，当前已有的查询仅能返回至少含列表中一种食材的结果，需要正确实现方案。

Ingredients
============
ID
IngredientName
IngredientAmount
P_ID


Dishes
==========
ID
DishName


DishIngredients
==========
ID
DishID
IngredientID

（仅能返回匹配任意一种食材的菜品）

DECLARE @IdIngredients nvarchar(1024) = '4174, 4028'
DECLARE @Ingredients TABLE (IdIngredient int) 
INSERT @Ingredients (IdIngredient)
SELECT Convert(int, value) FROM STRING_SPLIT(@IdIngredients, ',') 

SELECT DISTINCT D.Id
FROM Dishes D
INNER JOIN DishIngredients DI ON DI.DishID = D.ID
WHERE IngredientID IN (SELECT IdIngredient FROM @Ingredients)

DECLARE @IdIngredients nvarchar(1024) = '4174, 4028'
DECLARE @Ingredients TABLE (IdIngredient int) 
INSERT @Ingredients (IdIngredient)
SELECT Convert(int, value) FROM STRING_SPLIT(@IdIngredients, ',') 

SELECT DISTINCT D.Id
FROM Dishes D
INNER JOIN DishIngredients DI ON DI.DishID = D.ID
WHERE IngredientID IN (SELECT IdIngredient FROM @Ingredients)
GROUP BY D.Id
HAVING COUNT(D.Id) = (SELECT COUNT(*) FROM @Ingredients)

• 你给出的修改方案是可行的，只要满足DishIngredients表中不存在同一个菜品重复关联同一个食材ID的情况（绝大多数业务中菜品食材关联表都会加DishID + IngredientID唯一约束，符合这个前提）
• 可以去掉冗余的DISTINCT关键字，因为GROUP BY D.Id已经保证返回的菜品ID是唯一的，额外加DISTINCT会增加不必要的性能开销。
• 如果需要同时返回菜品名称等其他字段，直接在SELECT和GROUP BY子句中添加对应字段即可。

优化后代码 #

DECLARE @IdIngredients nvarchar(1024) = '4174, 4028'
DECLARE @Ingredients TABLE (IdIngredient int) 
INSERT @Ingredients (IdIngredient)
SELECT Convert(int, value) FROM STRING_SPLIT(@IdIngredients, ',') 

SELECT D.Id, D.DishName
FROM Dishes D
INNER JOIN DishIngredients DI ON DI.DishID = D.ID
WHERE DI.IngredientID IN (SELECT IdIngredient FROM @Ingredients)
GROUP BY D.Id, D.DishName
HAVING COUNT(DISTINCT DI.IngredientID) = (SELECT COUNT(*) FROM @Ingredients)

额外补充：如果你的关联表确实存在同菜品同食材重复录入的情况，把COUNT里换成COUNT(DISTINCT DI.IngredientID)就可以规避这个问题

其他可选实现（双重NOT EXISTS写法） #

如果你不想用分组计数的方式，也可以用如下写法，逻辑上是判断不存在任何一个指定食材是当前菜品没有的：

DECLARE @IdIngredients nvarchar(1024) = '4174, 4028'
DECLARE @Ingredients TABLE (IdIngredient int) 
INSERT @Ingredients (IdIngredient)
SELECT Convert(int, value) FROM STRING_SPLIT(@IdIngredients, ',') 

SELECT D.Id, D.DishName
FROM Dishes D
WHERE NOT EXISTS (
    SELECT 1 FROM @Ingredients I
    WHERE NOT EXISTS (
        SELECT 1 FROM DishIngredients DI 
        WHERE DI.DishID = D.ID AND DI.IngredientID = I.IdIngredient
    )
)

内容的提问来源于stack exchange，提问作者Gilles F