1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客（难度：中等）

题目描述

（通过次数9,522 | 提交次数12,882，通过率73.72%）

Customers表：
+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| customer_id         | int     |
| customer_name       | varchar |
+---------------------+---------+
customer_id 是这张表的主键。
customer_name 是顾客的名称。

Orders表：
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_name  | varchar |
+---------------+---------+
order_id 是这张表的主键。
customer_id 是购买了名为 "product_name" 产品顾客的id。

请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和customer_name ），我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照customer_id 排序。

查询结果如下例所示。
Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+
Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+
Result table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/customers-who-bought-products-a-and-b-but-not-c

//测试数据
Create table If Not Exists Customers (customer_id int, customer_name varchar(30));
Create table If Not Exists Orders (order_id int, customer_id int, product_name varchar(30));

insert into Customers (customer_id, customer_name) values ('1', 'Daniel');
insert into Customers (customer_id, customer_name) values ('2', 'Diana');
insert into Customers (customer_id, customer_name) values ('3', 'Elizabeth');
insert into Customers (customer_id, customer_name) values ('4', 'Jhon');

insert into Orders (order_id, customer_id, product_name) values ('10', '1', 'A');
insert into Orders (order_id, customer_id, product_name) values ('20', '1', 'B');
insert into Orders (order_id, customer_id, product_name) values ('30', '1', 'D');
insert into Orders (order_id, customer_id, product_name) values ('40', '1', 'C');
insert into Orders (order_id, customer_id, product_name) values ('50', '2', 'A');
insert into Orders (order_id, customer_id, product_name) values ('60', '3', 'A');
insert into Orders (order_id, customer_id, product_name) values ('70', '3', 'B');
insert into Orders (order_id, customer_id, product_name) values ('80', '3', 'D');
insert into Orders (order_id, customer_id, product_name) values ('90', '4', 'C');

解题思路

Orders表保存了用户所有的订单记录。
题目要求：查询出那些购买了产品 A 和产品 B 却没有购买产品 C 的顾客。
很明显，这是三个独立的过滤条件：购买产品A、购买产品B、没有购买产品C。
对于用户来说，存在某产品的购买记录，即表示购买了该产品。可以使用EXISTS关键字实现；不存在某产品的购买记录，即表示没有购买该产品。可以使用NOT EXISTS关键字实现。
那么，可以使用如下SQL来达到查询要求：

SELECT 
    a.customer_id,
    a.customer_name
FROM Customers a
WHERE EXISTS(SELECT 1 FROM Orders b WHERE a.customer_id = b.customer_id AND b.product_name = 'A')
  AND EXISTS(SELECT 1 FROM Orders b WHERE a.customer_id = b.customer_id AND b.product_name = 'B')
  AND NOT EXISTS(SELECT 1 FROM Orders b WHERE a.customer_id = b.customer_id AND b.product_name = 'C');

当然，我们也可以从另外一个思路来解决问题：计算出每个产品的购买次数，对于购买次数大于0，即表示购买；购买次数等于0，即表示没有购买。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

SELECT 
    a.customer_id,
    b.customer_name
FROM Orders a
INNER JOIN Customers b
ON a.customer_id = b.customer_id
GROUP BY a.customer_id 
HAVING COUNT(CASE WHEN a.product_name = 'A' THEN 1 END)>0
   AND COUNT(CASE WHEN a.product_name = 'B' THEN 1 END)>0
   AND COUNT(CASE WHEN a.product_name = 'C' THEN 1 END)=0;