【LC中等】1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈，孜孜不倦

1.题目描述

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 。

2.代码实现

• 方法一：分别筛选出购买A，B，C的顾客id，然后再对顾客id进行筛选

SELECT customer_id,customer_name 
FROM Customers 
WHERE 
customer_id IN(SELECT customer_id FROM Orders WHERE product_name='A') 
AND 
customer_id IN(SELECT customer_id FROM Orders WHERE product_name='B') 
AND 
customer_id NOT IN(SELECT customer_id FROM Orders WHERE product_name='C')

• 方法二：巧妙的运用having对分组后顾客的id进行sum筛选

select c.customer_id,  customer_name
from customers c join orders o
on c.customer_id = o.customer_id
group by customer_id
having sum(IF(product_name='A',1,0)) > 0 and sum(IF(product_name='B',1,0)) > 0 and sum(IF(product_name='C',1,0)) = 0 

简化思路

select c.customer_id,  customer_name
from customers c join orders o
on c.customer_id = o.customer_id
group by customer_id
having sum(product_name='A') > 0 and sum(product_name='B') > 0 and sum(product_name='C') = 0 

select c.customer_id,  customer_name
from customers c join orders o
on c.customer_id = o.customer_id
group by customer_id
having sum(product_name='A')  and sum(product_name='B')  and not sum(product_name='C')