在联接和包含子句后对结果进行分组

Question

假设我有三个表(mysql(：

食谱

+----+----------------+--------------+
| id |     title      |   image      |
+----+----------------+--------------+
|  2 | recipe title 1 | banana image |
|  3 | recipe title 2 | potato image |
+----+----------------+--------------+

成分

+----+-----------+---------+---------------+
| id | recipe_id | food_id | quantity_kg   |
+----+-----------+---------+---------------+
|  1 |         2 |      36 | 2.5           |
|  2 |         3 |      37 | 1.5           |
+----+-----------+---------+---------------+

食物

+----+---------+-------+-----------+----------+
| id |  name   | price | foodType  | unitType |
+----+---------+-------+-----------+----------+
| 36 | carrot  |     2 | vegetable | kg       |
| 37 | chicken |    12 | meat      | kg       |
+----+---------+-------+-----------+----------+

现在，我想得到所有素食食谱，即不包含任何食物类型为"肉"(或其他动物产品(的食物。

如何执行此类查询？

这是我到目前为止尝试过的：

SELECT
recipe.id as recipeId,
recipe.title as title,
food.type as foodType
FROM recipe
INNER JOIN ingredient on ingredient.recipe_id = recipe.id
INNER JOIN food on food.id = ingredient.aliment_id
HAVING 
food.type <> 'meat' AND
food.type <> 'seafood' AND
food.type <> 'fish' 
ORDER BY recipeId

这有效(我只得到素食食谱(，但它复制了所有食谱，只要它们有多种成分。

+----------+--------+----------+
| recipeId | title  | foodType |
+----------+--------+----------+
|        5 | titleA | type1    |
|        5 | titleA | type2    |
|        5 | titleA | type3    |
|        8 | titleB | type2    |
|        8 | titleB | type5    |
|        8 | titleB | type1    |
|        8 | titleB | type3    |
+----------+--------+----------+

我想得到的是：

+----------+--------+
| recipeId | title  |
+----------+--------+
|        5 | titleA |
|        8 | titleB |
+----------+--------+

我已经尝试在 SELECT 子句中删除"foodType"，但如果我这样做，mysql 告诉我："'有子句'中的未知列'food.type'">

我已经在 HAVING 子句之前尝试过按"recipeId"进行分组，但我得到错误："SELECT 列表的表达式 #3 不在 GROUP BY 子句中，并且包含非聚合列'myDb.food.type'，它在功能上不依赖于 GROUP BY 子句中的列"(我理解该错误(。

我想这与"加入并具有子句后对结果进行分组"之类的东西有关，但我可能是错的......

多谢

Answer 1

• 您没有GROUP BY子句，因此不应有HAVING子句。请改用WHERE
• 从SELECT中删除不需要的列
• 因为联接是跨 1：多关系的，但你只选择"一"端，所以你可能还需要SELECT DISTINT而不仅仅是SELECT

此外，您还有另一个问题：查询的逻辑实际上并不正确，即使它使用如此少量的示例数据返回明显正确的结果。

查看组合时，您可能希望使用EXISTS和子查询。也许像这样(未经测试(：

SELECT
recipe.id as recipeId,
recipe.title as title,
food.type as foodType
FROM recipe r
WHERE NOT EXISTS
(SELECT food.type
FROM ingredient INNER JOIN food on food.id = ingredient.aliment_id
WHERE ingredient.recipe_id = r.id AND 
food.type IN ('meat', 'seafood','fish')
)
ORDER BY recipeId

Answer 2

只排除所有至少含有一种肉类的食谱 (即使没有SQL，我也在10年前做到了(

SELECT recipe.id, recipe.title FROM recipe
WHERE recipe.id NOT IN (
SELECT
recipe.id,
FROM recipe
INNER JOIN ingredient 
on ingredient.recipe_id = recipe.id
INNER JOIN food 
on food.id = ingredient.aliment_id
AND food.type IN ('meat', 'seafood', 'fish') 
)
ORDER BY recipeId