文章目录
题目
代码(7.23 首刷看解析)
知识点:IFNULL(p1, p2)
:如果p1不为空,返回p1,反之返回10
先获取子表p1,这个表包括不重复的id
再获取子表p2,这个表包括不超过截止日期的最大日期
将二者连接起来(根据id)
# Write your MySQL query statement below
select p1.product_id, ifnull(p2.new_price, 10) price from
(select distinct product_id from Products) p1
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)) p2
on p1.product_id = p2.product_id