存储过程—在sql中，从一对多表中选择所有fk，其中fk的表数据存在于多个列表/联接中的每个列表/联接中

我的数据库中有带相关数据的配方（在一系列类别中，每个配方都为每个类别选择了[0-多]个选项）。要搜索配方，用户可以从[0-many]类别中选择[0-many]选项。
我正在尝试构造一个存储过程，该过程返回所有recipeID，这些RecipeID与用户选择了至少一个optionid的每个类别至少一个optionid匹配。
所以-如果你想找到所有主菜和水果甜点，proc需要返回所有recipeids，其中：
在recipedata中，对于具有相同recipeid的所有条目（对于单个配方的所有选项）
至少有一个optionid用于“主菜”或至少有一个optionid用于“甜点”
至少有一个optionid是“水果”
忽略“排名”（用户未选择此类别中的任何选项）
类别的数量是有限和有限的（目前有12个）。现在，我将用户的搜索查询作为12个表变量提供—每个类别一个，列出该类别所选的optionID。我更喜欢将用户的搜索查询作为单个表提交给proc，但我不确定这是否可行。不管怎样，这是一个较低的优先级。
必须可以构造一个查询来返回我要查找的内容，但我不知道如何做到这一点。我所能想到的一切都涉及到在组中循环（每个配方的recipedata、每个类别的选项），据我所知，sql并不是为实现这一点而构建的。
我可以在sql中这样做，还是必须在c代码中这样做？如果我能用sql来做这个-怎么做？
参数：

DECLARE @MealTypeOptionID TABLE ( OptionID INT )
DECLARE @IngredientOptionID TABLE ( OptionID INT )
DECLARE @RankingOptionID TABLE ( OptionID INT )
-- all 'Main Dish' or 'Dessert' recipes that have 'Fruit'
INSERT INTO @MealTypeOptionID (OptionID) VALUES (1), (2)
INSERT INTO @IngredientOptionID (OptionID) VALUES (4)

table：

Recipe
---------------------------------------------------------------
RecipeID    RecipeName
---------------------------------------------------------------
1           'Apple Pie'
2           'Blueberry Ice Cream'
3           'Brownies'
4           'Tuna Casserole'
5           'Pork with Apples'
6           'Fruit Salad'
Category
---------------------------------------------------------------
CategoryID    CategoryName
---------------------------------------------------------------
1             'Meal Type'
2             'Ingredients'
3             'Ranking'
Option
---------------------------------------------------------------
OptionID    CategoryID    OptionName
---------------------------------------------------------------
1           1             'Main Dish'
2           1             'Dessert'
3           1             'Side Dish'
4           2             'Fruit'
5           2             'Meat'
6           3             'Meh'
7           3             'Great'
RecipeData
---------------------------------------------------------------
RecipeDataID    RecipeID    OptionID
---------------------------------------------------------------
1               1           2
2               1           4
3               1           7
4               2           2
5               2           4
6               3           2
7               4           1
8               4           5
9               4           6
10              5           1
11              5           4
12              5           5
13              6           3
14              6           4

-- @optionsToInclude is a parameter of the proc
DECLARE @optionsToInclude TABLE (CategoryID INT, OptionID INT)
-- result table
DECLARE @recipeIDs TABLE (RecipeID INT)
-- get CategoryID FOR first select
DECLARE @categoryID INT
SELECT TOP 1 @categoryID = CategoryID FROM @optionsToInclude GROUP BY CategoryID
-- insert into result table all RecipeIDs that contain any OptionIDs within CategoryID
INSERT INTO @recipeIDs (RecipeID)
SELECT DISTINCT d.RecipeID
FROM RecipeData d
INNER JOIN @optionsToInclude c
ON c.CategoryID = @categoryID
AND c.OptionID = d.OptionID
-- delete from @optionsToInclude all entries where CategoryID = @categoryID
DELETE FROM @optionsToInclude WHERE CategoryID = @categoryID
-- check if any more Categories exist to loop through
DECLARE @exists BIT = 1
IF (NOT EXISTS (SELECT * FROM @optionsToInclude))
SET @exists = 0
WHILE @exists = 1
BEGIN
-- get CategoryID for select
SELECT TOP 1 @categoryID = CategoryID FROM @optionsToInclude GROUP BY CategoryID
-- delete from result table all RecipeIDs that do not contain any OptionIDs within CategoryID
DELETE FROM @recipeIDs
WHERE RecipeID NOT IN
(
SELECT DISTINCT d.RecipeID
FROM dbo.RecipeData d
INNER JOIN @optionsToInclude i
ON i.CategoryID = @categoryID
AND i.OptionID = d.OptionID
)
-- delete from @optionsToInclude all entries where CategoryID = @categoryID
DELETE FROM @optionsToInclude WHERE CategoryID = @categoryID
-- check if any more Categories exist to loop through
IF (NOT EXISTS (SELECT * FROM @optionsToInclude))
SET @exists = 0
END

存储过程—在sql中，从一对多表中选择所有fk，其中fk的表数据存在于多个列表/联接中的每个列表/联接中

1条答案

相关问题

热门标签

最新问答