Leetcode1045. 买下所有产品的客户（中等）

题目
Customer 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+

product_key 是 Product 表的外键。

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 。

创建数据

CREATE TABLE Customer2(
customer_id INT,
product_key INT
);

CREATE TABLE Product2(
product_key INT
);

INSERT INTO Customer2 VALUE(1, 5), (2, 6), (3,5), (3,6), (1,6);
INSERT INTO Product2 VALUE(5), (6);

SELECT *
FROM Customer2 AS C1
JOIN Customer2 AS C2
ON C1.`customer_id` = C2.`customer_id` AND C1.`product_key` <> C2.`product_key`;

审题
这个题目中的实例就两个产品
以下是一种不太通用的办法 但对于这个例子可以实现
先自连接两表 查询每个用户购买的产品类别

SELECT *
FROM Customer2 AS C1
LEFT JOIN Customer2 AS C2
ON C1.`customer_id` = C2.`customer_id` AND C1.`product_key` <> C2.`product_key`;

自连接把产品类别放在一行

SELECT *
FROM Product2 AS P1
JOIN Product2 AS P2
ON P1.`product_key` <> P2.`product_key`;

用二元in选出最后的结果

SELECT DISTINCT tmp.customer_id
FROM (SELECT C1.`customer_id`, C1.`product_key` AS pro1, C2.`product_key` AS pro2
FROM Customer2 AS C1
LEFT JOIN Customer2 AS C2
ON C1.`customer_id` = C2.`customer_id` AND C1.`product_key` <> C2.`product_key`) AS tmp
WHERE (tmp.pro1, tmp.pro2) IN (SELECT *
FROM Product2 AS P1
JOIN Product2 AS P2
ON P1.`product_key` <> P2.`product_key`)

但这个方法如果用在很多类别就不行了

转向商品数量
先算出商品总数

SELECT COUNT(DISTINCT P.`product_key`) AS all_num
FROM Product2 AS P;

对id进行分组 计算每一个顾客的商品数量

SELECT C.`customer_id`, COUNT(DISTINCT C.`product_key`) AS eve_num
FROM Customer2 AS C
GROUP BY C.`customer_id`;

两者结合一下即可

SELECT DISTINCT tmp.customer_id 
FROM (SELECT C.`customer_id`, COUNT(DISTINCT C.`product_key`) AS eve_num
FROM Customer2 AS C
GROUP BY C.`customer_id`) AS tmp
WHERE tmp.eve_num = (SELECT COUNT(DISTINCT P.`product_key`) AS all_num
FROM Product2 AS P);

对上边的进行优化 利用having

SELECT C.`customer_id`
FROM Customer2 AS C
GROUP BY C.`customer_id`
HAVING COUNT(DISTINCT C.`product_key`) = (SELECT COUNT(DISTINCT P.`product_key`) AS all_num
FROM Product2 AS P);