数据导入:
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');
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 。
思路
关键在于处理购买了 Product 表中所有产品的客户的 id。
这个所有怎么处理?
也就是每个customer_id 买的不同的产品数量 = Product表里面的产品数(这里product_key是主键)
答案
select customer_id from customer where product_key in (select product_key from Product) group by customer_id having count(distinct product_key) = (select count(*) from Product);
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/customers-who-bought-all-products
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。