力扣SQL50 指定日期的产品价格 双重子查询 coalesce

Problem: 1164. 指定日期的产品价格

• coalesce 的使用
  

简洁版

👨‍🏫 参考题解

select distinct p1.product_id,coalesce((select p2.new_pricefrom Products p2where p2.product_id = p1.product_id and p2.change_date <= '2019-08-16'order by p2.change_date DESClimit 1),10) as price 
from Products p1

高效版

👨‍🏫 参考题解

select p1.product_id, ifnull(p2.new_price, 10) as price
from (select distinct product_idfrom products
) as p1 -- 所有的产品
left join (select product_id, new_price from productswhere (product_id, change_date) in (select product_id, max(change_date)from productswhere change_date <= '2019-08-16'group by product_id)
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id