- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+ Product_id 是该表的主键(具有唯一值的列)。 该表的每一行表示每种产品的名称和价格。表:
Sales
+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+ 这个表可能有重复的行。 product_id 是 Product 表的外键(reference 列)。 buyer_id 永远不会是 NULL。 sale_date 永远不会是 NULL。 该表的每一行都包含一次销售的一些信息。编写一个解决方案,报告那些买了 S8 而没有买 iPhone 的 买家。注意,S8 和 iPhone 是
Product
表中显示的产品。以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入: Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+Sales
table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 1 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 3 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+ 输出: +-------------+ | buyer_id | +-------------+ | 1 | +-------------+ 解释: id 为 1 的买家购买了一部 S8,但是却没有购买 iPhone,而 id 为 3 的买家却同时购买了这 2 部手机。
三,建表语句
Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);
Truncate table Product;
insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000');
insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800');
insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400');
Truncate table Sales;
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '1', '3', '2019-06-02', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '3', '2019-05-13', '2', '2800')
四,分析
表格大法 内连接+子查询+去重
第一步:内连接两个表
第二步,分别取出 购买了S8和购买了Iphone的用户
第三步:子查询,not in过滤
第四步:去重 输出
第一步:内连接两个表
with t1 as (
select
s.product_id,s.buyer_id,p.product_name
from sales s join product p on s.product_id=p.product_id
)
select * from t1;
第二步,分别取出 购买了S8和购买了Iphone的用户
with t1 as (
select
s.product_id,s.buyer_id,p.product_name
from sales s join product p on s.product_id=p.product_id
)
# select * from t1;
,t2 as (
select * from t1 where product_name='S8' and buyer_id not in (select buyer_id from t1 where t1.product_name='iPhone')
)
select * from t2;
第三步:子查询,not in过滤
第四步:去重 输出
五,SQL解答
with t1 as (
select
s.product_id,s.buyer_id,p.product_name
from sales s join product p on s.product_id=p.product_id
)
# select * from t1;
,t2 as (
select * from t1 where product_name='S8' and buyer_id not in (select buyer_id from t1 where t1.product_name='iPhone')
)
# select * from t2;
select distinct buyer_id from t2;
六,验证
七,知识点总结
- 内连接的运用
- 子查询的运用
- 子查询过滤的运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用