leetcode-1398.购买了产品A和产品B却没有购买产品C的顾客

题目： 

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。

创建数据库：

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')

 查询原表

 Customers表：

 

  Orders表：

要求： 

 请你编写解决方案，报告购买了产品 "A"，"B" 但没有购买产品 "C" 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。

返回按 customer_id 排序 的结果表。

 分析：

首先，我们通过题意得出来我们一定不要买过产品"C"的用户，那么我们就筛选出买过产品"C"的用户id。 

select customer_id FROM orders where product_name='C'

 

然后，我们通过子查询的方式把上面筛选出来的id去除。得到除了买过产品"C"的用户信息

select *
        from orders
        where customer_id not in (select customer_id
                                  from orders
                                  where product_name = 'C')

 接着，我们得到除了买过产品"C"的用户信息，还有得到这些用户那些购买了产品 "A"，"B"，所以，我们可以再进行筛选出买过产品"A"和"B"的订单。筛选出来之后，我们对每个用户进行分组然后对product_name去重计数，看看那些用户的计数次数等于2。就能得出有那些用户。

with t1 as (select *
                    from orders
                    where customer_id not in (select customer_id
                                               from orders
                                               where product_name = 'C')
                     and (product_name = 'a' or product_name = 'b'))
select customer_id
                  from t1
                  group by customer_id
                  having count(distinct product_name)>= 2

最后，得到的用户就我们需要的用户。通过子查询方法可以把他的详细信息输出出来

最终代码：

select *
from customers
where customer_id in (with t1 as (select *
                                  from orders
                                  where customer_id not in (select customer_id
                                                            from orders
                                                            where product_name = 'C')
                                    and (product_name = 'a' or product_name = 'b'))
                      select customer_id
                      from t1
                      group by customer_id
                      having count(distinct product_name)>= 2);