1 题目描述

产品数据表: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+

(product_id, change_date) 是此表的主键（具有唯一值的列组合）。 这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。 以 任意顺序 返回结果表。

2 测试用例

输入：

Products 表:

+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+

输出：

+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

3 解题思路

1. 按照product_id分组统计， 在change_date <= '2019-08-16'场景下，每个product_id最后变更价格的日期

select tp1.product_id, tp1.new_price  
from Products as tp1  
	 inner join (select product_id, max(change_date) max_date  
				 from Products  
				 where change_date <= '2019-08-16'  
				 group by product_id) as tp2  
				on tp1.product_id = tp2.product_id and tp1.change_date = tp2.max_date

查询结果

+----------+---------+
|product_id|new_price|
+----------+---------+
|2         |50       |
|1         |35       |
+----------+---------+

2. 查询所有product_id并去重

select distinct product_id from Products

查询结果

+----------+
|product_id|
+----------+
|1         |
|2         |
|3         |
+----------+

3. 将步骤2的结果左外连接步骤1的结果，步骤2中的product_id数据包含步骤1的product_id，价格更新满足change_date > '2019-08-16'的产品对应的价格会为null

select p1.product_id, p2.new_price as price  
from (select distinct product_id from Products) as p1  
         left join (select tp1.product_id, tp1.new_price  
                    from Products as tp1  
                             inner join (select product_id, max(change_date) max_date  
                                         from Products  
                                         where change_date <= '2019-08-16'  
                                         group by product_id) as tp2  
                                        on tp1.product_id = tp2.product_id and tp1.change_date = tp2.max_date) as p2  
                   on p1.product_id = p2.product_id

查询结果

+----------+-----+
|product_id|price|
+----------+-----+
|1         |35   |
|2         |50   |
|3         |null |
+----------+-----+

4. 按照题目意思，默认初始价格为10，需要对价格为null的数据设置初始值10

select p1.product_id, ifnull(p2.new_price, 10) as price  
from (select distinct product_id from Products) as p1  
         left join (select tp1.product_id, tp1.new_price  
                    from Products as tp1  
                             inner join (select product_id, max(change_date) max_date  
                                         from Products  
                                         where change_date <= '2019-08-16'  
                                         group by product_id) as tp2  
                                        on tp1.product_id = tp2.product_id and tp1.change_date = tp2.max_date) as p2  
                   on p1.product_id = p2.product_id

查询结果

+----------+-----+
|product_id|price|
+----------+-----+
|1         |35   |
|2         |50   |
|3         |10   |
+----------+-----+