力扣---高频SQL题---34---1164. 指定日期的产品价格

一、题目

二、代码

# Write your MySQL query statement below

select Products.product_id , Products.new_price as price  from Products
join
(
    select product_id,max(change_date) as ma from Products
    where change_date  <= '2019-08-16'
    group by product_id 
-- having change_date = max(change_date)
) as table2
where Products.product_id  = table2.product_id
    and Products.change_date  = ma
union
select distinct product_id ,10 as price from Products
where product_id not in
(
    select distinct product_id from Products
    where change_date  <= '2019-08-16'
)

三、运行结果