- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用
目录
一,原题力扣链接
二,题干
Customers
表:+---------------------+---------+ | Column Name | Type | +---------------------+---------+ | customer_id | int | | customer_name | varchar | +---------------------+---------+ customer_id 是这张表中具有唯一值的列。 customer_name 是顾客的名称。
Orders
表:+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | customer_id | int | | product_name | varchar | +---------------+---------+ order_id 是这张表中具有唯一值的列。 customer_id 是购买了名为 "product_name" 产品顾客的id。请你编写解决方案,报告购买了产品 "A","B" 但没有购买产品 "C" 的客户的 customer_id 和 customer_name,因为我们想推荐他们购买这样的产品。
返回按
customer_id
排序 的结果表。返回结果格式如下所示。
示例 1:
输入: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Daniel | | 2 | Diana | | 3 | Elizabeth | | 4 | Jhon | +-------------+---------------+ Orders table: +------------+--------------+---------------+ | order_id | customer_id | product_name | +------------+--------------+---------------+ | 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 | +------------+--------------+---------------+ 输出: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 3 | Elizabeth | +-------------+---------------+ 解释: 只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ,却没有购买产品 C 。
三,建表语句
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');
select * from customers;
select * from orders;
四,分析
题解:
第一张表:顾客表
字段:顾客id,顾客姓名
第二张表:订单表
字段:订单id,顾客id,产品名称
要求
购买了A和B产品,但是没有购买C产品的 顾客id 和顾客姓名
思路
第一步,有多个顾客id,要分析每个顾客购买的产品 是否满足A和B 没有购买C 所以要对顾客id进行分组
第二步,拼接该分组的列,用group_concat 拼接
第三步
- 第一种方案 用字符串判断函数 locate 如果存在 返回索引,如果不存在返回0
- 第二种该方案 ,用模糊查询 满足 like ‘A’ ,like ‘B’,不满足‘C’
最后拿到满足条件的id
第四步,根据id 查询顾客表 可能有多个满足的id 所以需要用in拿到顾客的id 和顾客的信息
第五步,对顾客id做一个排序
五,SQL解答
解法一, group_concat+locate+子查询
with t1 as (
select customer_id,group_concat(product_name) as gc from orders group by customer_id
)
select customer_id,customer_name from customers where customer_id in (
select customer_id as c_id from t1 where LOCATE('A',gc)>0 and LOCATE('B',gc)>0 and LOCATE('C',gc)=0
)
order by customer_id;
解法二,groop_concat+like+内连接
with t1 as (
select customer_id,
group_concat(product_name) gc
from orders
group by customer_id having gc like '%A%' and gc like '%B%' and gc not like '%C%'
),t2 as (
select c.customer_id as c_id,c.customer_name as c_name from customers c join t1 on c.customer_id=t1.customer_id
)
select c_id as customer_id , c_name as customer_name from t2 order by customer_id;
六,验证
解法一
解法二
七,知识点总结
group_concat 把一列数据变成一行的的运用
locate字符串索引函数的运用
like模糊查询的运用
多条件判定,满足A和B但是不满足C
内连接的运用
子查询的运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用