mysql - SQL:确切行匹配给定某些元素

这是我第一次在这里发布。我似乎找不到解决我问题的答案。

所以...我正在为一个学校项目安排一个数据库，该食谱仅显示可以使用“架子”中现有元素制作的食谱。
这些成分必须精确匹配。

用户：

+---------------+------+----------+----------+
|     email     | name | lastname | password |
+---------------+------+----------+----------+
| [email protected]  | John | Potato   | password |
| [email protected] | Mary | Carrot   | password |
+---------------+------+----------+----------+

架：

+---------+------------+---------------+
| shelfID | ingredient |     user      |
+---------+------------+---------------+
|       1 |          1 | [email protected]  |
|       2 |          2 | [email protected]  |
|       3 |          3 | [email protected]  |
|       4 |          4 | [email protected]  |
|       5 |         10 | [email protected] |
|       6 |         12 | [email protected] |
+---------+------------+---------------+

这是我的recipe_ingredient关系表

recipe_ingredient：

+--------+------------+
| recipe | ingredient |
+--------+------------+
|      1 |          1 |
|      1 |          2 |
|      1 |          4 |
|      1 |         10 |
|      2 |          1 |
|      2 |          2 |
|      2 |          3 |
|      2 |          4 |
|      3 |          2 |
|      3 |          3 |
|      3 |         15 |
+--------+------------+

我已经尝试过以下查询：

    SELECT
    rec_ing.recipe, shf.ingredient, shf.user
FROM
    recipes_ingredients AS rec_ing
        INNER JOIN
    shelf AS shf ON rec_ing.ingredient = shf.ingredient
        INNER JOIN
    users AS usr ON shf.user = usr.email
WHERE
    usr.email = '[email protected]'

返回此表：

+--------+------------+--------------+
| recipe | ingredient |     user     |
+--------+------------+--------------+
|      1 |          1 | [email protected] |
|      1 |          2 | [email protected] |
|      1 |          4 | [email protected] |
|      2 |          1 | [email protected] |
|      2 |          2 | [email protected] |
|      2 |          3 | [email protected] |
|      2 |          4 | [email protected] |
|      3 |          2 | [email protected] |
|      3 |          3 | [email protected] |
+--------+------------+--------------+

尽管食谱1确实包含我架子上的食材，但也缺少食材10

+--------+------------+
| recipe | ingredient |
+--------+------------+
|      1 |          1 |
|      1 |          2 |
|      1 |          4 |
|      1 |         10 |
|    ... |        ... |
+--------+------------+

我试图只得到这种结果集。

+--------+------------+
| recipe | ingredient |
+--------+------------+
|      2 |          1 |
|      2 |          2 |
|      2 |          3 |
|      2 |          4 |
+--------+------------+

因为食谱2包含了我书架中的所有元素

我整天忙于解决这个问题...可能遗漏了什么？

最佳答案

这有点棘手，因为您需要按配方汇总当前查询，但是还需要原始查询来取回完整记录。可悲的是，MySQL不支持常见的表表达式或其他功能，这些功能可能会使我们的查询更为冗长。

SELECT
    rec_ing.recipe,
    shf.ingredient,
    shf.user
FROM recipes_ingredients AS rec_ing
INNER JOIN shelf AS shf
    ON rec_ing.ingredient = shf.ingredient
INNER JOIN users AS usr
    ON shf.user = usr.email
INNER JOIN
(
    SELECT rec_ing.recipe
    FROM recipes_ingredients AS rec_ing
    INNER JOIN shelf AS shf
        ON rec_ing.ingredient = shf.ingredient
    LEFT JOIN users AS usr
        ON shf.user = usr.email
    WHERE
        usr.email = '[email protected]'
    GROUP BY rec_ing.recipe
    HAVING COUNT(usr.email) = COUNT(*)
) t
    ON rec_ing.recipe = t.recipe
WHERE
    usr.email = '[email protected]'

这里的基本策略是对子查询做一个附加的联接，该子查询标识所有配方属于给定用户的所有配方。关键部分如下：

HAVING COUNT(usr.email) = COUNT(*)

这将检查给定配方的总行数是否与已分配给给定用户的行数匹配。

关于mysql - SQL:确切行匹配给定某些元素，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44937633/