时间戳

首页 > 解决方案 > 加入和有子句后的分组结果

mysql - 加入和有子句后的分组结果

问题描述

假设我有三个表（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       |
+----+---------+-------+-----------+----------+

现在，我想获取所有素食食谱，即不包含任何 foodType 为“肉”（或其他动物产品）的食物。

如何执行此类查询？

这是我迄今为止尝试过的：

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 会告诉我：“'having 子句'中的未知列 'food.type'”

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

我想这与“加入和拥有子句后的分组结果”之类的东西有关，但我可能错了......

非常感谢

标签： mysqljoingroup-by

解决方案

  • 你没有GROUP BY从句，所以你不应该有HAVING从句。WHERE改为使用
  • 从您的SELECT
  • 因为连接跨越 1:many 关系，但您只在“一个”方面进行选择，您可能还想要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

推荐阅读