1 题目描述
Customer 表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
该表可能包含重复的行
customer_id 不为 NULL
product_key 是 Product 表的外键 (reference 列)
Product 表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key 是这张表的主键 (具有唯一值的列)
编写解决方案, 报告 Customer 表中购买了 Product 表中所有产品的客户的 id
2 测试用例
输入:
Customer 表:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
+-------------+-------------+
Product 表:
+-------------+
| product_key |
+-------------+
| 5 |
| 6 |
+-------------+
输出:
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
解释:
购买了所有产品 (5 和 6) 的客户的 id 是 1 和 3
3 解题思路
- 分组统计顾客购买的商品数量
product_key, 但要考虑到可能重复购买商品, 需要对商品信息去重discinct product_key, 然后再统计Product中商量总数, 只要满足顾客购买的商品数量等于商品表中的总数据
select customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(product_key) from Product)
查询结果
+-----------+
|customer_id|
+-----------+
|1 |
|3 |
+-----------+
