力扣之1398.购买了产品A和产品B却没有购买产品C顾客

题目：

Sql 建表语句：

Create table If Not Exists Customers (customer_id int, customer_name varchar(30))
Create table If Not Exists Orders (order_id int, customer_id int, product_name varchar(30))
Truncate table Customers
insert into Customers (customer_id, customer_name) values ('1', 'Daniel')
insert into Customers (customer_id, customer_name) values ('2', 'Diana')
insert into Customers (customer_id, customer_name) values ('3', 'Elizabeth')
insert into Customers (customer_id, customer_name) values ('4', 'Jhon')
Truncate table Orders
insert into Orders (order_id, customer_id, product_name) values ('10', '1', 'A')
insert into Orders (order_id, customer_id, product_name) values ('20', '1', 'B')
insert into Orders (order_id, customer_id, product_name) values ('30', '1', 'D')
insert into Orders (order_id, customer_id, product_name) values ('40', '1', 'C')
insert into Orders (order_id, customer_id, product_name) values ('50', '2', 'A')
insert into Orders (order_id, customer_id, product_name) values ('60', '3', 'A')
insert into Orders (order_id, customer_id, product_name) values ('70', '3', 'B')
insert into Orders (order_id, customer_id, product_name) values ('80', '3', 'D')
insert into Orders (order_id, customer_id, product_name) values ('90', '4', 'C')

分析：看到题之后，我们可以先把商品表按照顾客id分组，分完组后拼接每个组内的商品，然后使用like判断是否满足条件。图文分析：

sql实现：

with t1 as (--  把订单表按照顾客id分组，然后拼接商品名select customer_id,group_concat(product_name) a from Orders group by customer_id 
),t2 as (-- 然后筛选出里面包含A包含B但是不包含C的信息select * from t1 where a like '%A%' AND a like '%B%' AND a not like '%C%')
select c1.customer_id,c1.customer_name from t2 ,Customers c1 where t2.customer_id=c1.customer_id
-- 连接顾客表，得到顾客姓名

Pandas 例子：

data = [[1, 'Daniel'], [2, 'Diana'], [3, 'Elizabeth'], [4, 'Jhon']]
customers = pd.DataFrame(data, columns=['customer_id', 'customer_name']).astype({'customer_id':'Int64', 'customer_name':'object'})
data = [[10, 1, 'A'], [20, 1, 'B'], [30, 1, 'D'], [40, 1, 'C'], [50, 2, 'A'], [60, 3, 'A'], [70, 3, 'B'], [80, 3, 'D'], [90, 4, 'C']]
orders = pd.DataFrame(data, columns=['order_id', 'customer_id', 'product_name']).astype({'order_id':'Int64', 'customer_id':'Int64', 'product_name':'object'})

pandas实现:

import pandas as pddef find_customers(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:-- --  把订单表按照顾客id分组，然后拼接商品名a=orders.groupby('customer_id')['product_name'].sum().reset_index()-- 然后筛选出里面包含A包含B但是不包含C的信息a=a[a['product_name'].str.contains('A',case=True) & a['product_name'].str.contains('B',case=True) & ~a['product_name'].str.contains('C',case=True)]-- 连接顾客表，得到顾客姓名b=a.merge(customers, on='customer_id', how='inner')b=b[['customer_id','customer_name']]return b