使用 MySQL 中另一个表的多个条件对不同记录进行计数

Question

这不是家庭作业。我更改了表和字段的名称，仅供说明之用。我承认我对 MySQL 完全陌生。请在您的回答中考虑这一点。

说明我需要的查询功能的最佳方式是这样的：

我有两个表。

一个表与另一表的关系为 0..1 到 0..n。

仅为了简单起见，假设这两个表是配方和成分。

成分表中的字段之一引用配方表，但可能为空。

仅举个例子：

我想知道类似以下内容的 SQL：有多少食谱需要“橄榄”的数量“1”和“蘑菇”的数量为“2”

作为结构化查询语言的新手，我什至不知道如何通过 google 搜索此信息。

我是否走在正确的轨道上？：

SELECT COUNT(DISTINCT Recipe.ID) AS Answer FROM Recipe, Ingredient 
  WHERE Ingredient.RecipeID=Recipe.ID AND Ingredient.Name='Olives'
  AND Ingredient.Amount=1 AND Ingredient.Name='Mushrooms'
  AND Ingredient.Amount=2

我意识到这是完全错误的，因为名称不能同时是橄榄和蘑菇......但不知道该放什么，因为我需要计算所有食谱，但只所有食谱都包含两者。

我怎样才能正确地为 MySQL 编写这样的查询？

Answer 1

你很接近。

尝试类似的方法

SELECT  COUNT(Recipe.ID) Answer
FROM    Recipe INNER JOIN
        Ingredient olives ON olives.RecipeID=Recipe.ID INNER JOIN
        Ingredient mushrooms ON mushrooms.RecipeID=Recipe.ID 
WHERE   olives.Name='Olives'
AND     mushrooms.Name='Mushrooms'
AND     olives.Amount = 1
AND     mushrooms.Amount = 2

您可以两次加入同一个表，您所需要做的就是为表提供适当的 别名。

Answer 2

使用：

SELECT COUNT(r.id)
  FROM RECIPE r
 WHERE EXISTS(SELECT NULL
                FROM INGREDIENTS i
               WHERE i.recipeid = r.id
                 AND i.name = 'Olives'
                 AND i.amount = 1)
   AND EXISTS(SELECT NULL
                FROM INGREDIENTS i
               WHERE i.recipeid = r.id
                 AND i.name = 'Mushrooms'
                 AND i.amount = 2)

Answer 3

SELECT COUNT(DISTINCT Recipe.ID) AS Answer 
FROM Recipe, Ingredient as ing1, Ingredient as ing2
WHERE 
  Ing1.RecipeID=Recipe.ID AND Ing1.Name="Olives" AND ing1.Amount=1 AND 
  Ing2.RecipeID=Recipe.ID AND Ing2.Name="Mushrooms" AND ing2.Amount=2;

希望这有帮助

Answer 4

首先，您使用旧的连接语法。 INNER JOIN 创建相同的执行计划，但更加清晰。然后，您的查询都是关于简单的旧条件。你只需要把它们写正确就可以了！ （我同意，这需要一些练习。）

SELECT COUNT(DISTINCT R.ID) Answer
FROM Recipe R
INNER JOIN Ingredient I
    ON R.ID = I.RecipeID
    AND (R.Name = 'Olives' AND I.Amount = 1)
    OR (R.Name = 'Mushrooms' AND I.Amount = 2);

这是我的示例数据：

mysql> SELECT * FROM Ingredient;
+------+------+--------+----------+
| ID   | Name | Amount | RecipeID |
+------+------+--------+----------+
|    1 | I1   |      1 |        1 |
|    1 | I2   |      2 |        1 |
|    1 | I3   |      2 |        1 |
|    1 | I4   |      3 |        1 |
|    1 | I1   |      1 |        2 |
|    1 | I2   |      1 |        2 |
|    1 | I3   |      3 |        2 |
|    1 | I4   |      2 |        2 |
|    1 | I1   |      2 |        3 |
|    1 | I2   |      1 |        3 |
+------+------+--------+----------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM Recipe;
+------+-----------+
| ID   | Name      |
+------+-----------+
|    1 | Mushrooms |
|    2 | Olives    |
|    3 | Tomatoes  |
+------+-----------+
3 rows in set (0.00 sec)

我的查询输出 2，正如它应该的那样。

编辑：实际上，我意识到我的查询选择了错误的行。这工作正常：

SELECT COUNT(DISTINCT R.Id) Answer 
FROM Recipe R 
INNER JOIN Ingredient I 
    ON R.ID = I.RecipeID 
WHERE 
    (R.Name = 'Mushrooms' AND I.Amount = 2) 
    OR (R.Name = 'Olives' AND I.Amount = 1);

它也输出 2。

Answer 5

仅供参考...

有多少食谱需要“1”份量的“橄榄”和“2”份量的“蘑菇”

查看上面的表格结构，您可能想要一个关联实体在食谱和配料之间。这使得使用内部查询来执行您正在寻找的查询变得更加容易加入。

在你的两张桌子之间，我会想象这样的东西......

Recipe_Ingredient
-----------------
(PK, FK) RecipeID
(PK, FK) IngredientID

如果你有这样的东西，那么每个食谱可能有很多成分，并且每种成分可能是许多食谱的一部分。一旦您有了这样的表格来正确关联两个表格，您就可以将它们连接在一起并获得完整的配方。对于像这样的更复杂的查询，其中两个食谱有单独的条件，我可能会对其进行子查询以帮助理解。

SELECT SUM(RecipeCount) as RecipeCount
FROM
(
    SELECT COUNT(r.*) as RecipeCount
    FROM Recipe r
    INNER JOIN Recipe_Ingredient ri on r.ID = ri.RecipeID
    INNER JOIN Ingredient i on i.ID = ri.IngredientID
    WHERE i.Name = 'Olives' AND i.Amount = 1
UNION ALL
    SELECT COUNT(r.*) as RecipeCount
    FROM Recipe r
    INNER JOIN Recipe_Ingredient ri on r.ID = ri.RecipeID
    INNER JOIN Ingredient i on i.ID = ri.IngredientID
    WHERE i.Name = 'Mushrooms' AND i.Amount = 2
) as subTable