41 产品销售分析 III

[astak16]

题目

编写一个 SQL 查询，选出每个销售产品的 第一年 的 product_id、 first_year、 quantity ****和 price。

• sales 表中的主键是 sale_id 外键是 product_id
• product 表中的主键是 product_id

ps：如果第一年有两个价格也需要查询出来

CREATE TABLE sales (
	sale_id INT,
	product_id INT,
	year INT,
	quantity INT,
	price INT 
);
INSERT INTO sales ( sale_id, product_id, year, quantity, price ) VALUES
( 1, 100, 2008, 10, 5000 ),
( 2, 100, 2009, 12, 5000 ),
( 7, 200, 2011, 15, 9000 );
	
CREATE TABLE product ( 
	product_id INT, 
	product_name VARCHAR ( 10 ) 
);
INSERT INTO product ( product_id, product_name ) VALUES
( 100, 'Nokia' ),
( 200, 'Apple' ),
( 300, 'Samsung' );

分析

1. 需要先知道第一年 first_year 和产品id product_id
2. product 表用不到

SQL：方法一

select
	product_id,
	year as first_year,
	quantity,
	price
from (
	select 
		product_id,
		year,
		quantity,
		price,
		rank() over(partition by product_id order by year) as rn
	from sales
) as temp where rn = 1;

解析

1. 使用窗口函数 rank()，按照 product_id 分组， year 升序排序，得到 rn
   1. 如果第一年有多个价格，都需要查询出来，所以这里不能使用 row_number
2. 将第一步查询出来的表作为临时表 temp
3. 查询临时表 temp 筛选出 rn = 1 的数据，因为 rn 是升序排序的，所以 rn = 1 就是第一年

SQL：方法二

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

解析

1. 使用 group by 按照 product_id 进行分组，查询出 product_id 和第一年 first_year
2. 查询 sales 表，筛选出第一步中查询出来的 product_id 和 first_year

SQL：方法三

select
	sales.product_id,
	first_year,
	quantity,
	price
from (
	select product_id, min(year) first_year from sales group by product_id
) as temp 
left join sales on temp.product_id = sales.product_id 
and temp.first_year = sales.year;

解析

1. 使用 group by 按照 product_id 进行分组，查询出 product_id 和第一年 first_year
2. 将第一步作为临时表 temp 和 sales 左连，连接条件是 product_id 和 first_year = year