买下所有产品的客户

1045. 买下所有产品的客户

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 。

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

pythondata 解法, 执行用时: 272 ms, 内存消耗: 60.3 MB, 提交时间: 2023-09-17 10:43:06

import pandas as pd

def find_customers(customer: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    ans = customer.groupby('customer_id')['product_key'].nunique().to_frame().reset_index()
    return ans[ans['product_key'] == len(product['product_key'].values)]['customer_id'].to_frame()

def find_customers2(customer: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    customer_buy = customer.groupby('customer_id')[['product_key']].nunique()
    product_count = product['product_key'].count()
    return customer_buy[customer_buy['product_key']==product_count].index.to_frame()
    
def find_customers3(customer: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    sum_df = customer.groupby('customer_id')['product_key'].nunique().reset_index()
    return sum_df[sum_df['product_key']==product['product_key'].nunique()][['customer_id']]

mysql 解法, 执行用时: 364 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:16:40

# Write your MySQL query statement below
SELECT customer_id FROM customer
	GROUP BY customer_id
	HAVING COUNT(DISTINCT product_key)=(
		SELECT COUNT(DISTINCT product_key) FROM product)

mysql 解法, 执行用时: 389 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:16:15

# Write your MySQL query statement below
select customer_id
from Customer
group by customer_id
having count(distinct product_key) in 
(select count(distinct product_key) from Product)

mysql 解法, 执行用时: 371 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:15:58

# Write your MySQL query statement below
SELECT 
    customer_id 
FROM 
    customer
GROUP BY customer_id
HAVING count(DISTINCT product_key) = (
    SELECT 
        count(DISTINCT product_key) 
    FROM product
);

上一题

下一题

详情