查询指定日期的全部商品价格
题目需求
从商品价格修改明细表(sku_price_modify_detail)中查询2021-10-01的全部商品的价格,假设所有商品初始价格默认都是99。期望结果如下:
sku_id(商品id) | price(商品价格) |
---|---|
1 | 2000.00 |
2 | 10.00 |
3 | 5000.00 |
4 | 6000.00 |
5 | 500.00 |
6 | 2000.00 |
7 | 100.00 |
8 | 600.00 |
9 | 1000.00 |
10 | 90.00 |
11 | 66.00 |
12 | 20.00 |
代码实现
select
si.sku_id,
nvl(new_price, 99) price
from sku_info si
left join
(
select
sku_id,
new_price
from
(
select
sku_id,
new_price,
row_number() over (partition by sku_id order by change_date desc) rn
from sku_price_modify_detail
where change_date <= '2021-10-01'
)t1
where rn=1
)t2
on si.sku_id=t2.sku_id;