mysql - SQL - 如何在没有子选择的情况下排除结果？

Question

我坚持使用 sql 查询来列出购买产品 a 但没有购买产品 b 的客户。这是我的 table

表客

id_customer   customer_name
1             name1
2             name2
3             name3

表格顺序

id_order   id_customer  product
1          1            a
2          1            b
3          2            b
4          3            a

我尝试:

SELECT * FROM customer, order WHERE customer.id_customer = order.id_customer
AND (order.product='a' AND order.product<>'b')

SELECT * FROM customer, order WHERE customer.id_customer = order.id_customer
AND (order.product IN ('a') AND order.product NOT IN ('b'))
[AND (order.product = 'a' AND order.product <> 'b')]

SELECT table1.id_customer, table1.customer_name FROM customer INNER JOIN order ON customer.id_customer = order.id_customer
WHERE order.product IN ('a') AND order.product NOT IN ('b')
[WHERE order.product = 'a' AND order.product <> 'b']

但它没有正确答案，因为它返回:

1          1            a
4          3            a

答案应该是:

4          3            a

请大家帮帮我。非常感谢

Answer 1

您可以使用连接来过滤出客户是否拥有每个产品 a、b，然后查询连接以实现您的特定逻辑。它看起来像这样:

select distinct   -- pull only unique customer information
    C.*
from
    customer C
left join   -- orders of product a, which should exist
    order OA on OA.id_customer = C.id_customer and OA.product = 'a'
left join   -- orders of product b, which should not exist
    order OB on OB.id_customer = C.id_customer and OB.product = 'b'
where       -- orders of product a should exist
    OA.id_order is not null
and         -- orders of product b should not exist
    OB.id_order is null