LeetCode 1164.指定日期的产品价格

需求

写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

数据准备

drop table Products;

Create table If Not Exists Products (product_id int, new_price int, change_date date);
Truncate table Products;
insert into Products (product_id, new_price, change_date) values ('1', '20', '2019-08-14');
insert into Products (product_id, new_price, change_date) values ('2', '50', '2019-08-14');
insert into Products (product_id, new_price, change_date) values ('1', '30', '2019-08-15');
insert into Products (product_id, new_price, change_date) values ('1', '35', '2019-08-16');
insert into Products (product_id, new_price, change_date) values ('2', '65', '2019-08-17');
insert into Products (product_id, new_price, change_date) values ('3', '20', '2019-08-18');

输入

分析

输出

1. 获取日期小于指定时间的数据

with t1 as (
    select * ,
       row_number() over (partition by product_id order by change_date desc ) rn1
    from Products
    where change_date<='2019-08-16'
)
select *
from t1
where rn1=1

2. 右链接 获取需要数据

with t1 as (
    select * ,
       row_number() over (partition by product_id order by change_date desc ) rn1
    from Products
    where change_date<='2019-08-16'
),t2 as (
    select *
    from t1
    where rn1=1
)
select p.*
from t2 right join Products p
on t2.product_id=p.product_id
where t2.product_id is null

3. 关联两个表,获取最终数据

with t1 as (
    select * ,
       row_number() over (partition by product_id order by change_date desc ) rn1
    from Products
    where change_date<='2019-08-16'
),t2 as (
    select product_id,new_price,change_date
    from t1
    where rn1=1
),t3 as (
    select p.*
    from t2 right join Products p
    on t2.product_id=p.product_id
    where t2.product_id is null
)
select product_id,new_price as price from t2
union all
select product_id,'10' as price from t3
;