sqlite - 查找可以由一组成分制成的食谱 [英] sqlite - find recipes that can be made from a set of ingredients

问题描述

现在我在 ios 应用程序中使用 sqlite，我希望能够搜索可以从成分列表中制作的食谱(即，食谱是所提供的成分的子集)

Right now I am using sqlite within a ios application, and I want to be able to search for recipes that can be made from a list of ingredients (ie recipes such that are a subset of the provided ingredients)

例如:

Recipe 1: A B C
Recipe 2: A B
Recipe 3: C D
Recipe 4: A
Recipe 5: E

Query for ingredients A B C returns recipes {1, 2, 4}
Query for ingredients A B returns recipes {2, 4}
Query for ingredients D returns {}

目前我设置的是

Table Items
name text primary key

Table Recipes
ID required_item integer, recipe_name text

我可以轻松查询包含三种成分中任何一种的食谱以及查询包含所有三种成分的食谱，但我无法弄清楚如何仅查询

I can easily do queries for recipes containing any of the three ingredients and queries for recipes containing all of the three ingredients, but I haven't been able to figure out how to do queries for only

Recipes ∈ P(Ingredients)

抱歉，我是数据库编程的新手，如果有任何帮助，我们将不胜感激

Sorry, I'm new to database programming, and any help would be greatly appreciated

推荐答案

您可以使用 left join 到搜索表.然后group by 配方.使用 sharing 子句来要求每个食谱都没有不在搜索列表中的成分.例如:

You could use a left join to a search table. Then group by recipe. Use a having clause to demand that for each recipe, there are no ingredients that are not in the search list. For example:

select  ri.RecipeNr
from    RecipeIngredients ri
left join
        (
        select  'A' as Ingredient
        union all
        select  'B'
        union all
        select  'C'
        ) search
on      ri.Ingredient = search.Ingredient
group by
        ri.RecipeNr
having  count(case when search.Ingredient is null then 1 end) = 0

SQL Fiddle 的实例.

这篇关于sqlite - 查找可以由一组成分制成的食谱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！