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。
请你编写解决方案，报告购买了产品 "A"，"B" 但没有购买产品 "C" 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。
返回按 customer_id 排序 的结果表。
2 测试用例
输入：
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 |
+------------+--------------+---------------+
输出：
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3 | Elizabeth |
+-------------+---------------+
解释： 只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。
3 解题思路
- 使用内连接
Orders和Customers，通过使用group by对customer_id和customer_name分组统计购买的product_name
select c.customer_id, c.customer_name, group_concat(distinct o.product_name)
from Customers as c
inner join Orders as o on o.customer_id = c.customer_id
group by c.customer_id, c.customer_name
执行结果
+-----------+-------------+-------------------------------------+
|customer_id|customer_name|group_concat(distinct o.product_name)|
+-----------+-------------+-------------------------------------+
|1 |Daniel |A,B,C,D |
|2 |Diana |A |
|3 |Elizabeth |A,B,D |
|4 |Jhon |C |
+-----------+-------------+-------------------------------------+
- 分析步骤 1 中顾客购买的 product_name，可以使用 having 过滤用户购买的 product_name 信息，查找购买了 A 和 B，但没有购买 C 的顾客
select c.customer_id, c.customer_name, group_concat(distinct o.product_name)
from Customers as c
inner join Orders as o on o.customer_id = c.customer_id
group by c.customer_id, c.customer_name
having group_concat(distinct product_name) like '%A%'
and group_concat(distinct product_name) like '%B%'
and group_concat(distinct product_name) not like '%C%'
执行结果
+-----------+-------------+-------------------------------------+
|customer_id|customer_name|group_concat(distinct o.product_name)|
+-----------+-------------+-------------------------------------+
|3 |Elizabeth |A,B,D |
+-----------+-------------+-------------------------------------+
- 步骤 2 已经查找出符合要求的顾客信息，把购买的 product_name 信息去掉就符合题目要求了
select c.customer_id, c.customer_name
from Customers as c
inner join Orders as o on o.customer_id = c.customer_id
group by c.customer_id, c.customer_name
having group_concat(distinct o.product_name) like '%A%'
and group_concat(distinct o.product_name) like '%B%'
and group_concat(distinct o.product_name) not like '%C%'
order by c.customer_id;
执行结果
+-----------+-------------+
|customer_id|customer_name|
+-----------+-------------+
|3 |Elizabeth |
+-----------+-------------+
还有一种解法，使用 sum 统计每个顾客购买 product_name A ，B，C 的数据量，最终需要满足 购买 A 和 B 的数量 > 0，C 的数量 = 0
select c.customer_id, c.customer_name
from Customers as c
inner join Orders as o on o.customer_id = c.customer_id
group by c.customer_id, c.customer_name
having sum(o.product_name = 'A') > 0
and sum(o.product_name = 'B') > 0
and sum(o.product_name = 'C') = 0
order by c.customer_id;
