Leetcode SQL 1164.指定日期的产品价格（不使用union）

题目：
写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10。

样例数据：
Products table:

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

Result table:

product_id	price
2	50
1	35
3	10

解题思路：

1. 题目要求

• 小于等于8月16日之前的价格（取最后一次）
• 如果8月16日之前没有价格记录则仍然使用初始价格：10

2. 解答思路

• 第一步：筛选出8月16日前每个产品最后一次修改数量的日期

select p.product_id, max(p.change_date) as change_date
from Products p where p.change_date <= "2019-08-16"
group by p.product_id

• 第二步：匹配这个产品最后一次修改日期的价格

select p.product_id, p.new_price
from Products p
where p.change_date <= "2019-08-16"
and (p.product_id, p.change_date) in
(select p.product_id, max(p.change_date) as change_date
from Products p where p.change_date <= "2019-08-16"
group by p.product_id)

• 第三步：左连接Products表格（使用distinct做id去重），同时使用ifnull函数，将左连接结果中价格为null的替换成10

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

完整答案

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

注：文章题目来源于leetcode（https://leetcode-cn.com/problems/product-price-at-a-given-date/）