sql - 具有少量组选项的多对多关系查询

Question

我的多对多关系表有几组选项，我想创建 SQL 查询来获取产品 ID，每个组中至少包含一个选项。

+------------+-----------+
| product_id | option_id |
+------------+-----------+
| 1          | 1         |
| 1          | 3         |
| 2          | 2         |
| 3          | 4         |
+------------+-----------+

我尝试过这样的事情：

SELECT product_id
FROM options_to_products
WHERE (option_id = 1 OR option_id = 2)
AND (option_id = 3 OR option_id = 4)

它什么也没给，但我想看到1。任何想法如何进行这样的查询？

标签： sqlmany-to-manyrelation

Answer 1

一种方法使用条件聚合：

SELECT product_id
FROM options_to_products
GROUP BY product_id
HAVING
    SUM(CASE WHEN option_id IN (1, 2) THEN 1 ELSE 0 END) > 0 AND
    SUM(CASE WHEN option_id IN (3, 4) THEN 1 ELSE 0 END) > 0;

我们也可以尝试做一个自我加入：

SELECT DISTINCT otp1.product_id
FROM options_to_products otp1
INNER JOIN options_to_products otp2
    ON otp1.product_id = otp2.product_id AND
       (otp1.option_id IN (1, 2) AND otp2.option_id IN (3, 4) OR
       otp1.option_id IN (3, 4) AND otp2.option_id IN (1, 2));