mysql - 根据关系表中的标志过滤所有记录

Question

好的，我有两张 table

产品:

+------------+----------------+
| product_id | name           |
+------------+----------------+
|          1 | A product here |
+------------+----------------+
|          2 | B product here |
+------------+----------------+

和卖家

+------------+----------------+------------+
| seller_id  | in_stock       | product_id |
+------------+----------------+------------+
|          1 | 1              | 1          |
+------------+----------------+------------+
|          2 | 0              | 1          |
+------------+----------------+------------+
|          2 | 0              | 2          |
+------------+----------------+------------+

我正在尝试弄清楚如何仅退回所有卖家都有 in_stock = 0 的产品。

因此，在此特定示例中，它只会返回 Product_id 为 2 的产品，因为 Product_id 有一行 in_stock=1

Answer 1

该产品的 in_stock 列的最大值必须为 0:

SELECT 
    product_id
FROM
    sellers
GROUP BY
    product_id
HAVING
    MAX(in_stock) = 0;

因此您可以通过联接获取产品信息

SELECT
    *
FROM
    products
INNER JOIN (
   SELECT 
        product_id
    FROM
        sellers
    GROUP BY
        product_id
    HAVING
        MAX(in_stock) = 0
) t
ON
    products.product_id = t.product_id;