MySQL独占行选择

Question

我有一个名为“survey_product”的表，其结构如下：

id int(11)
order_id int(11)
product_id int(11)
pq1 varchar(2)
pq2 varchar(2)
pq3 varchar(2)

此表存储通过系统订购的产品。

下面是该表中的一些数据记录：

(1, 2, 20, '1', '1', 'y')
(2, 2, 21, '1', 'y', 'y')
(3, 2, 22, '1', 'y', 'n')
(4, 2, 23, '1', 'y', 'y')
(5, 2, 24, '1', 'n', 'y')
(6, 3, 20, '1', 'n', 'y')
(7, 3, 24, '1', 'n', 'y')
(8, 3, 25, '1', 'n', 'y')
(9, 4, 20, '1', 'n', 'y')
(10, 4, 21, '1', 'n', 'y')
(11, 4, 23, '1', 'n', 'y')
(12, 4, 24, '1', 'n', 'y')

以上我们有3个订单（订单号2、3和4）。

我需要获取product\u id=21但product\u id=20的所有订单的order\u id（订单中的其他product\u id不相关-我只对21和20感兴趣）。

然后我需要知道product\u id为20和21的订单（同样，订单中的其他产品是不相关的）。

这是目前提取特定产品的所有订单的基本查询：

SELECT order_id FROM survey_product 
WHERE product_id = 20
AND (pq1 = '1' OR pq1 = '2' OR pq1 = '3')

关于我如何实现这一目标，有什么想法吗？

我试过了

SELECT order_id FROM survey_product 
WHERE product_id IN (20) AND product_id NOT IN (21)
AND (pq1 = '1' OR pq1 = '2' OR pq1 = '3')

但它也排除了所有其他值。

谢谢。

Answer 1

我通常通过在WHERE条件中使用嵌套查询来做到这一点：

SELECT order_id
FROM survey_product
WHERE product_id = 21
AND pq1 IN ('1', '2', '3')
AND order_id NOT IN (
    SELECT order_id 
    FROM survey_product
    WHERE product_id = 20)

基本上，您可以从product\u id=20的行中找到所有order\u id，然后将其从主查询中排除，也就是说，您可以从product\u id=21的行中选择order\u id，但不在子查询产生的order\u id中。

Answer 2

在这种情况下，使用JOIN而不是嵌套查询通常是有益的：

    SELECT sp.order_id
      FROM survey_product AS sp
 LEFT JOIN 
           (SELECT order_id FROM survey_product WHERE product_id = 20) AS sp_t
        ON sp.order_id = sp_t.order_id
     WHERE sp_t.order_id IS NULL
       AND sp.product_id = 21
       AND sp.pq1 IN ('1', '2', '3')

获取order_id在product_id中同时包含20和21的查询类似：您只需使用INNER JOIN而无需检查sp_t.order_id。或者保持原样，但如果您很懒，请将IS NULL替换为IS Not NULL-但我想这会运行得更慢。）

这里有一把SQL小提琴可以玩。