题目
Table: Customers
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------------+---------+
customer_id is the primary key for this table.
customer_name is the name of the customer.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_name | varchar |
+---------------+---------+
order_id is the primary key for this table.
customer_id is the id of the customer who bought the product "product_name".
Write an SQL query to report the customer_id and customer_name of customers who bought products "A", "B" but did not buy the product "C" since we want to recommend them buy this product.
Return the result table ordered by customer_id.
The query result format is in the following example.
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 |
+-------------+---------------+
Only the customer_id with id 3 bought the product A and B but not the product C.
解答
先选出同时购买了A和B的顾客id
select distinct O1.customer_id
from Orders as O1
join Orders as O2
on O1.customer_id = O2.customer_id
where O1.product_name = 'A' and O2.product_name = 'B'
再选出购买了C的顾客id
select distinct O1.customer_id
from Orders as O1
where O1.product_name = 'C'
对Customers 选出 id在{同时购买了A和B的顾客id} 但不在{购买了C的顾客id}的即可
select C.customer_id, C.customer_name
from Customers as C
where C.customer_id in (select distinct O1.customer_id
from Orders as O1
join Orders as O2
on O1.customer_id = O2.customer_id
where O1.product_name = 'A' and O2.product_name = 'B' ) and
C.customer_id not in (select distinct O1.customer_id
from Orders as O1
where O1.product_name = 'C')
