用最新值替换空值 [英] Replace null value by latest value

问题描述

说我有下表.如果我的连接表与日期和货币不匹配，我如何获取最新的先前值?在空 DKK 值上，我希望它选择 3.请注意，日期并非每天都存在，因为我不在周末加载表.

Say I have below table. How do I pick up the latest previous value in case my joined table to not match the date and currency? On the null DKK value I want it to pick up 3. Note that dates do not exist every day since I do not load the tables on weekends.

Select
    PositionDate,
    Currency,
    T2.Value,
    isnull(t2.value, ? )
From t1
left join t2
on t1.currency = t2.Currency
and t1.PositionDate = t2.PositionDate

.

PositionDate    Currency        Value
2017-04-11      SEK               1
2017-04-11      DKK               NULL
2017-04-11      EUR               7
2017-04-10      SEK               4 
2017-04-10      DKK               3
2017-04-10      EUR               5
2017-04-07      SEK               4 
2017-04-07      DKK               3
2017-04-07      EUR               5

.

推荐答案

您可以使用 CTE 和 Case 条件来实现.

You can Achieve it by using CTE and Case condition.

With cte as
(
    Select
        PositionDate,
        Currency,
        T2.Value,
    From t1
    left join t2
    on t1.currency = t2.Currency
    and t1.PositionDate = t2.PositionDate
        and t1.PositionDate = t2.PositionDate
)
select PositionDate, Currency, Value, 
   CASE WHEN ISNULL(value,'')='' THEN
    (Select top 1 from cte cin where cin.currency=cout.currency order by CONVERT(Date,PositionDate) desc)
   ELSE
    Value 
   END as Value2 
   From cte cout

这篇关于用最新值替换空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！