1045 买下所有产品的客户
SQL架构
Create table If Not Exists Customer_1045 (customer_id int, product_key int);
Create table Product_1045 (product_key int);
Truncate table Customer_1045;
insert into Customer_1045 (customer_id, product_key) values ('1', '5');
insert into Customer_1045 (customer_id, product_key) values ('2', '6');
insert into Customer_1045 (customer_id, product_key) values ('3', '5');
insert into Customer_1045 (customer_id, product_key) values ('3', '6');
insert into Customer_1045 (customer_id, product_key) values ('1', '6');
Truncate table Product_1045;
insert into Product_1045 (product_key) values ('5');
insert into Product_1045 (product_key) values ('6');
Customer 表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
product_key 是 Customer 表的外键。
Product 表:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key 是这张表的主键。
写一条 SQL 查询语句,从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。
示例:
Customer 表:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
+-------------+-------------+
Product 表:
+-------------+
| product_key |
+-------------+
| 5 |
| 6 |
+-------------+
Result 表:
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
购买了所有产品(5 和 6)的客户的 id 是 1 和 3 。
解题
select
customer_id
from
Customer_1045
group by customer_id
having count(distinct product_key) in
(
select count(distinct product_key) from Product_1045
);