时间戳

首页 > 解决方案 > 用连接中的最后一个可用值替换零值

sql - 用连接中的最后一个可用值替换零值

问题描述

SQL-FIDDLE 链接

我正在使用 SQL Server。

编辑：之前使用了错误的 SQL Fiddle - 更新为更正一个

加入声明：

select t1.A_NR, t1.V_DATE, t1.AMOUNT T1_AMOUNT, t2.AMOUNT T2_AMOUNT
from Table_1 t1 
left join Table_2 t2 on t1.A_NR = t2.A_NR and t1.V_DATE = t2.V_DATE

在 T2_Amount 行中给我带来了这个带有空值的表。

+------+----------------------+-----------+-----------+
| A_NR |        V_DATE        | T1_AMOUNT | T2_AMOUNT |
+------+----------------------+-----------+-----------+
|    1 | 2020-01-01T00:00:00Z |       100 | 100       |
|    1 | 2020-01-02T00:00:00Z |       101 | (null)    |
|    1 | 2020-01-03T00:00:00Z |       102 | (null)    |
|    2 | 2020-01-01T00:00:00Z |       200 | 200       |
|    2 | 2020-01-02T00:00:00Z |       201 | (null)    |
|    2 | 2020-01-03T00:00:00Z |       202 | (null)    |
+------+----------------------+-----------+-----------+

我想用 Table_2 中的最后一个可用值替换这些值，如下所示：

+------+----------------------+-----------+-----------+
| A_NR |        V_DATE        | T1_AMOUNT | T2_AMOUNT |
+------+----------------------+-----------+-----------+
|    1 | 2020-01-01T00:00:00Z |       100 |       100 |
|    1 | 2020-01-02T00:00:00Z |       101 |       100 | -->  value from 01.01.2020
|    1 | 2020-01-03T00:00:00Z |       102 |       100 | -->  value from 01.01.2020
|    2 | 2020-01-01T00:00:00Z |       200 |       200 |
|    2 | 2020-01-02T00:00:00Z |       201 |       200 | -->  value from 01.01.2020
|    2 | 2020-01-03T00:00:00Z |       202 |       200 | -->  value from 01.01.2020
+------+----------------------+-----------+-----------+

标签： sqlsql-serverdatetimeleft-joingaps-and-islands

解决方案

一种选择使用相关子查询或横向连接：

select t1.a_nr, t1.v_date, t1.amount as t1_amount, t2.* 
from table_1 t1 
outer apply (
    select top (1) t2.amount as t2_amount
    from table_2 t2 
    where t2.a_nr = t1.a_nr and t2.v_date <= t1.v_date
    order by t2.v_date desc
) t2

另一种方法是使用一些间隙和孤岛技术：我们可以将不匹配的记录与带有窗口计数的最新匹配记录一起放入组中，然后使用窗口最大值来恢复我们想要的值：

select a_nr, v_date, amount as t1_amount, 
    max(t2_amount) over(partition by a_nr, grp)
from (
    select t1.*, t2.amount as t2_amount,
        count(t2.amount) over(partition by t1.a_nr order by t1.v_date) as grp
    from table_1 t1 
    left join table_2 t2 on t2.a_nr = t1.a_nr and t2.v_date = t1.v_date
) t

推荐阅读