2021-05-12

销售分析

Create table If Not Exists 45_Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists 45_Sales (seller_id int, product_id int,buyer_id int, sale_date date, quantity int, price int);
Truncate table 45_Product;
insert into 45_Product (product_id, product_name, unit_price) values (1, 'S8', 1000);
insert into 45_Product (product_id, product_name, unit_price) values (2, 'G4', 800);
insert into 45_Product (product_id, product_name, unit_price) values (3, 'iPhone', 1400);
Truncate table 45_Sales;
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (1, 1, 1,'2019-01-21', 2, 2000);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (1, 2, 2,'2019-02-17', 1, 800);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (2, 1, 3,'2019-06-02', 1, 800);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (3, 3, 3,'2019-05-13', 2, 2800);

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

展示效果：

product_id	product_name
1	S8
2	G4

最终sql：

SELECT 
      product_id,
      product_name
FROM
    (SELECT 
           s.product_id,
           product_name,
           sale_date
     FROM 
           45_Sales s
     JOIN 
           45_Product p
     ON 
           s.product_id = p.product_id
     GROUP BY 
           s.product_id
     HAVING 
           sale_date >= '2019-01-01' 
           AND 
           sale_date <= '2019-03-31') t