leecode 数据库：1070. 产品销售分析 III

导入数据：

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

销售表 Sales：

+-------------------+--------+
| Column Name | Type  |
+-------------------+--------+
| sale_id            | int      |
| product_id       | int      |
| year                 | int      |
| quantity           | int      |
| price               | int       |
+-------------------+--------+
(sale_id, year) 是这张表的主键。
product_id 是产品表的外键。
这张表的每一行都表示：编号 product_id 的产品在某一年的销售额。
请注意，价格是按每单位计的。

产品表 Product：

+--------------------+----------+
| Column Name  | Type    |
+--------------------+----------+
| product_id        | int        |
| product_name | varchar |
+--------------------+----------+
product_id 是这张表的主键。
这张表的每一行都标识：每个产品的 id 和 产品名称。

编写一个 SQL 查询，选出每个销售产品 第一年 销售的 产品 id、年份、数量 和 价格。

结果表中的条目可以按 任意顺序 排列。

查询结果格式如下例所示：

示例 1：

输入：
Sales 表：
+----------+---------------+------+-----------+-------+
| sale_id | product_id | year | quantity | price |
+----------+---------------+------+------------+-------+ 
| 1           | 100           | 2008 | 10         | 5000  |
| 2           | 100           | 2009 | 12         | 5000  |
| 7           | 200           | 2011 | 15         | 9000  |
+----------+--------------+-------+-----------+--------+
Product 表：
+--------------+--------------------+
| product_id | product_name |
+--------------+--------------------+
| 100           | Nokia               |
| 200           | Apple               |
| 300           | Samsung         |
+--------------+--------------------+
输出：
+--------------+-------------+-----------+--------+
| product_id | first_year | quantity | price |
+--------------+-------------+------------+-------+ 
| 100           | 2008        | 10          | 5000  |
| 200           | 2011        | 15          | 9000  |
+--------------+------------+------------+---------+

分析
需要先知道第一年 first_year 和产品id product_id
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;

解析
使用窗口函数 rank()，按照 product_id 分组， year 升序排序，得到 rn
如果第一年有多个价格，都需要查询出来，所以这里不能使用 row_number
将第一步查询出来的表作为临时表 temp
查询临时表 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
);

解析
使用 group by 按照 product_id 进行分组，查询出 product_id 和第一年 first_year
查询 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;

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

 来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/product-sales-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。