LeetCode 1084.销售分析3

数据准备

drop table Product;
drop table Sales;


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', '2', '3', '2019-06-02', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800');

条件

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

输入

输出

-- 编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。
-- 以 任意顺序 返回结果表。

-- 查询日期不在指定区间中的数据的id
select p.product_id
from Product p , Sales s
where p.product_id=s.product_id and (sale_date < '2019-01-01' or sale_date> '2019-03-31')

-- 查询所有日期在指定区间中的数据,并与上一步查询出来的id关联,排除掉日期同时出现在
-- 指定区间中和指定区间外的数据
select s.*,p.product_name,price
from Product p , Sales s
where p.product_id=s.product_id
and (sale_date between '2019-01-01' and '2019-03-31')
and p.product_id not in (
    select p.product_id
    from Product p , Sales s
    where p.product_id=s.product_id and (sale_date < '2019-01-01' or sale_date> '2019-03-31')
    )
;

-- 根据查询结果,显示所需要的信息
select p.product_id,product_name
from Product p , Sales s
where p.product_id=s.product_id
and (sale_date between '2019-01-01' and '2019-03-31')
and p.product_id not in (
    select p.product_id
    from Product p , Sales s
    where p.product_id=s.product_id and (sale_date < '2019-01-01' or sale_date> '2019-03-31')
    )