leetcode-SQL-1164. 指定日期的产品价格

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
这张表的主键是 (product_id, change_date)。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

输入：
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    |
+------------+-------+

select
    distinct p.product_id,
    ifnull(t.new_price,10) as price
from Products as p
left join(
    select
        product_id,
        new_price
    from Products
    where (product_id,change_date) in(
        select
            product_id,
            max(change_date)
        from Products
        where change_date<='2019-08-16'
        group by product_id
    )
)as t
on p.product_id=t.product_id;