当前位置：首页 > article >正文

Mysql基础练习题 1084.销售分析3 （力扣）

article 2025/1/23 14:14:36

编写解决方案，报告 2019年春季才售出的产品。即仅在 2019-01-01 （含）至 2019-03-31 （含）之间出售的商品

题目链接：

https://leetcode.cn/problems/sales-analysis-iii/description/

建表插入数据：

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')

画图分析：

代码实现：

#最终代码
select p.product_id as product_id,product_name from product p join sales s
    on p.product_id=s.product_id
                                  group by product_id,product_name
                                  having max(s.sale_date )<='2019-03-31' and min(s.sale_date) >='2019-01-01';

总结：

仅在 2019-01-01 （含）至 2019-03-31 （含）之间出售的商品，因此售卖日期最大的日期小于等于2019-03-31，最小的日期要大于等于2019-01-01

查看全文

http://www.kler.cn/a/286782.html