48，SQL训练之，力扣，1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

凡梦_leo

于 2024-08-02 15:53:17 发布

阅读量756

点赞数 12

分类专栏： SQL实战训练文章标签： leetcode 算法数据库服务器 sql linux c语言

本文链接：https://blog.csdn.net/qq_55006020/article/details/140872127

版权

SQL实战训练专栏收录该内容

69 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新

练习：知识的实践应用

解法一, group_concat+locate+子查询

解法二,groop_concat+like+内连接

六，验证

七，知识点总结

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

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;