MySQL 从多对多关系枢轴中选择父行

Question

我有三个表:products、ingredients 和 ingredient_product。

我需要找到所有具有相关成分的产品。它还需要在百分比列上有一个匹配的值。

一种产品存在两种相关成分。

+-----+------------+---------------+------------+
| id  | product_id | ingredient_id | percentage |
+-----+------------+---------------+------------+
|   1 |          1 |            1 |         50  |
|   2 |          1 |            2 |         50  |
+------------------+--------------+-------------+

检索 SQL:

SELECT
      products.id
    FROM
      products,
      ingredient_product
    WHERE
        ingredient_product.product_id = products.id
    AND
        (ingredient_product.ingredient_id = 1 AND ingredient_product.percentage = 50)
    AND
        (ingredient_product.ingredient_id = 2 AND ingredient_product.percentage = 50)

但这会返回一个空结果。 空集(0.00秒)

产品:

+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| id                | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)          | NO   |     | NULL    |                |
| short_description | text                  | YES  |     | NULL    |                |
| long_description  | text                  | YES  |     | NULL    |                |
+-------------------+-----------------------+------+-----+---------+----------------+

成分:

+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| id                | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| short_name        | varchar(50)           | NO   |     | NULL    |                |
| thumbnail         | varchar(255)          | NO   |     | NULL    |                |
+-------------------+-----------------------+------+-----+---------+----------------+

成分产品

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| product_id    | int(10) unsigned    | NO   | MUL | NULL    |                |
| ingredient_id | int(10) unsigned    | NO   | MUL | NULL    |                |
| percentage    | tinyint(3) unsigned | NO   |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+

Answer 1

基于草莓的回答:

SELECT product_id 
FROM ingredient_product 
WHERE ingredient_id IN (1,2) AND percentage = 50
GROUP BY product_id 
HAVING COUNT(*) = 2;

但是，如果您需要单独的百分比，则这不起作用，但您可以这样做:

SELECT product_id FROM
    (SELECT product_id 
    FROM ingredient_product 
    WHERE ingredient_id = 1 AND percentage = 50
    UNION ALL
    SELECT product_id 
    FROM ingredient_product 
    WHERE ingredient_id = 2 AND percentage = 50) AS tmp
GROUP BY product_id 
HAVING COUNT(*) = 2;

本质上，您为每个需求添加一个UNION(由 ID 和百分比组合组成)，然后将 HAVING 条件增加到需求数量。

注意:由于在这种情况下要求是互斥的，因此 UNION ALL 比 UNION 更快，并且会给出相同的结果。

您可以使用OR代替子查询中的UNION，但我们发现MySQL似乎更喜欢这种格式。然而，这可能会因版本而异。为了完整起见，这里也提供了该解决方案:

SELECT product_id 
FROM ingredient_product 
WHERE (ingredient_id = 1 AND percentage = 50) OR (ingredient_id = 2 AND percentage = 50)
GROUP BY product_id 
HAVING COUNT(*) = 2;