2021-05-04

买下所有产品的用户

需求：编写一个 SQL 查询，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

展示效果：

customer_id
1
3

Create table If Not Exists 41_Customer (customer_id int, product_key int);
Create table 41_Product (product_key int);
Truncate table 41_Customer;
insert into 41_Customer (customer_id, product_key) values (1, 5);
insert into 41_Customer (customer_id, product_key) values (2, 6);
insert into 41_Customer (customer_id, product_key) values (3, 5);
insert into 41_Customer (customer_id, product_key) values (3, 6);
insert into 41_Customer (customer_id, product_key) values (1, 6);
Truncate table 41_Product;
insert into 41_Product (product_key) values (5);
insert into 41_Product (product_key) values (6);

最终SQL:

select
	c1.customer_id
from
	41_Customer c1
join
	41_Product c2
on
	c1.product_key = c2.product_key
group by 
	c1.customer_id