LeetCode_sql_day09（1164.指定日期的产品价格）-CSDN博客

本文链接：https://blog.csdn.net/weixin_54353942/article/details/140756027

描述：1164.指定日期的产品价格

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10

输出如下：

数据准备：

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')

分析：

①先将数据根据product_id排序，同时算出change_date与2019-08-16两日期的差值
select product_id,
                   new_price,
                   change_date,
                   datediff(change_date, '2019-08-16') diff
            from Products
            order by product_id
②根据diff筛选出小于等于0 的（即在2019-08-16及之前的数据），在被筛选后的数据中找出max(change_date)即离2019-08-16最近的。
select *
         from t1
         where diff <= 0
select product_id,
       new_price,
       change_date,
       max(change_date) over(partition by product_id) r2
from t2
select * from t3 where r2 = change_date
③将具有完整product_id的表与该表连接，同时用ifnull(new_price,10)完善数据
select t5.product_id,
       ifnull(new_price,10) price
from(select distinct product_id from products)t5
    left join t4 on t5.product_id = t4.product_id

代码：

with t1 as (select product_id,
                   new_price,
                   change_date,
                   datediff(change_date, '2019-08-16') diff
            from Products
            order by product_id)
, t2 as (select *
         from t1
         where diff <= 0)
,t3 as (
select product_id,
       new_price,
       change_date,
       max(change_date) over(partition by product_id) r2
from t2
)
, t4 as (select * from t3 where r2 = change_date)
select t5.product_id,
       ifnull(new_price,10) price
from(select distinct product_id from products)t5
    left join t4 on t5.product_id = t4.product_id
;