[Leetcode 1045] Customers Who Bought All Products

原题说明

Table: Customer

+————-+———+
| Column Name | Type    |
+————-+———+
| customer_id | int     |
| product_key | int     |
+————-+———+
product_key is a foreign key to Product table.

Table: Product

+————-+———+
| Column Name | Type    |
+————-+———+
| product_key | int     |
+————-+———+
product_key is the primary key column for this table.

 

Write an SQL query for a report that provides the customer ids from the Customer table that bought all the products in the Product table.

For example:

Customer table:
+————-+————-+
| customer_id | product_key |
+————-+————-+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+————-+————-+

Product table:
+————-+
| product_key |
+————-+
| 5           |
| 6           |
+————-+
Result table:
+————-+
| customer_id |
+————-+
| 1           |
| 3           |
+————-+
The customers who bought all the products (5 and 6) are customers with id 1 and 3.

解题思路

筛选customer的条件为：其购买的distinct的商品数量等于Product中商品的数量
注意：

1. primary key是unique的
2. foreign key我们默认是存在的
3. 一般customer与product是多对多的关系，在实际应用中会有一张中间transaction的表

示例代码 (mysql)

# Write your MySQL query statement below
select customer_id 
from Customer
group by customer_id
having count(distinct(product_key)) = (select count(*) from Product);

归纳总结

我们在Youtube上更新了视频讲解，欢迎关注！