2021-05-06

产品销售分析

需求一：获取产品表 Product 中所有的 产品名称 product name 以及 该产品在 Sales 表中相对应的 上市年份 year 和 价格 price。

展示效果：

product_name	year	price
Nokia	2008	5000
Nokia	2009	5000
Apple	2011	9000

Create table 43_Sales (sale_id int, product_id int, year int, quantity int, price int);
Create table 43_Product (product_id int, product_name varchar(10));
Truncate table 43_Sales;
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (1, 100, 2008, 10, 5000);
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (2, 100, 2009, 12, 5000);
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (7, 200, 2011, 15, 9000);
Truncate table 43_Sales;
insert into 43_Product (product_id, product_name) values (100, 'Nokia');
insert into 43_Product (product_id, product_name) values (200, 'Apple');
insert into 43_Product (product_id, product_name) values (300, 'Samsung');

最终SQL:

select
	t2.product_name,
	t1.year,
	t1.price
from
	43_Sales t1
join
	43_Product t2
on 
	t1.product_id = t2.product_id

需求二：按产品 id（product_id ）来统计每个产品的销售总量。

展示效果：

product_id	total_quantity
100	22
200	15

最终SQL:

SELECT
    product_id, 
    SUM(quantity) as total_quantity
FROM
    43_Sales
GROUP BY
    product_id;

需求三：选出每个销售产品的第一年 的 产品 id、年份、数量 和 价格。

展示效果：

product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000

最终SQL:

select 
      product_id,
      year as first_year, 
      quantity,
      price
from 
      43_Sales
where 
     (product_id , year) in(
                            select
                                  product_id ,
                                  min(year)
                            from
                                  43_Sales
                            group by
                                  product_id
                            );