力扣高频SQL 50题(基础版)第三十题
1045.买下所有产品的客户
题目说明
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。
返回结果表 无顺序要求 。
实现过程
准备数据
Create table If Not Exists Customer (customer_id int, product_key int)
Create table Product (product_key int)
Truncate table Customer
insert into Customer (customer_id, product_key) values ('1', '5')
insert into Customer (customer_id, product_key) values ('2', '6')
insert into Customer (customer_id, product_key) values ('3', '5')
insert into Customer (customer_id, product_key) values ('3', '6')
insert into Customer (customer_id, product_key) values ('1', '6')
Truncate table Product
insert into Product (product_key) values ('5')
insert into Product (product_key) values ('6')
实现方式
select c.customer_id
from Customer c join Product p
on c.product_key=p.product_key
group by c.customer_id
having count(distinct c.product_key)=(select count(distinct product_key) from Product);