56 买下所有产品的客户

[astak16]

题目

题目链接：买下所有产品的客户

编写解决方案，报告 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');

Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
该表可能包含重复的行。
customer_id 不为 NULL。
product_key 是 Product 表的外键(reference 列)。

Product 表：
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
输出：
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
product_key 是这张表的主键（具有唯一值的列）。

解释：
购买了所有产品（5 和 6）的客户的 id 是 1 和 3 。

本题考察了 2 个知识点：

1. 如何查询出一个用户购买了多少件商品
2. 如何关联两张表中的数据

SQL

方法一

1. 全部有多少件商品：

• SELECT COUNT(*) FROM product;

2. 每个用户购买了哪些商品：

• SELECT customer_id, group_concat(product_key) AS product_key FROM Customer GROUP BY customer_id;

3. 分组之后通过 having 过滤掉购买商品数量不等于全部商品数量的用户：

• 第一个 count 需要使用 distinct 去重，可能会出现同一个用户买了多件商品
• 第二个 count 不需要去重，因为商品不会重复
  COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  count( DISTINCT product_key ) = ( SELECT count(*) FROM Product );

方法二

1. 先通过 group by coustomer_id 分组
2. 分组之后，可以使用 group_concat 函数将商品编号拼接成字符串

• group_concat( DISTINCT product_key ORDER BY product_key )：将 Customer 表中每个用户购买的商品编号拼接成字符串
• SELECT group_concat( product_key ORDER BY product_key ) FROM Product：将 Product 表中的商品编号拼接成字符串

3. 然后比较两个字符串是否相等

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  group_concat( DISTINCT product_key ORDER BY product_key ) = ( SELECT group_concat( product_key ORDER BY product_key ) FROM Product )