题24:
根据下表写一段SQL来查找在2019-08-16时全部产品的价格,假设所有产品在修改前的价格都是10。
其中
- 上表的主键是:product id,change_date;
- 上表的每一行分别记录了某产品在某个日期更改后的新价格。
解题思路:就是需要找到2019-08-16前所有有改动的产品及其最新价格和没有修改过价格的产品。
(1)找出所有的产品
SELECT DISTINCT product_id
from products
(2)找出产品最新修改的时间:max(),条件是时间要小于等于2019-08-16
select prodyct_id max(change_date)
from products
where change_date <='2019-08-16'
group by product_id
(3)利用where子查询,根据product-id和change_date找到对应的价格
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
)
(4)上面三步已经找到了所有的产品和已经修改过价格的产品。使用left join得到所有产品的最新价格,如果没有设置为10(利用ifnull函数)
select a.product_id, ifnull(b.new_price, 10) as price
from (
select distinct product_id
from products
) as a
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 b
on a.product_id = b.product_id;