You need to enable JavaScript to run this app. SQL Server左连接T2_AMOUNT空值替换为同组最近Table2值
优惠活动
大模型
产品
解决方案
定价
更多

SQL Server左连接T2_AMOUNT空值替换为同组最近Table2值

问题说明

当前使用的数据库环境为SQL Server,此前曾修正过旧版本查询的逻辑偏差,核心关联查询语句如下:

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列存在null空值,实际返回结果如下:

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

需求目标

将T2_AMOUNT列的null空值替换为对应A_NR分组下、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 | -- 取自2020-01-01的对应值
|    1 | 2020-01-03T00:00:00Z |       102 |       100 | -- 取自2020-01-01的对应值
|    2 | 2020-01-01T00:00:00Z |       200 |       200 |
|    2 | 2020-01-02T00:00:00Z |       201 |       200 | -- 取自2020-01-01的对应值
|    2 | 2020-01-03T00:00:00Z |       202 |       200 | -- 取自2020-01-01的对应值
+------+----------------------+-----------+-----------+

实现方案

可根据实际使用的SQL Server版本,选择对应的兼容实现:

SQL Server 2022及以上版本

直接使用原生支持忽略空值的LAST_VALUE窗口函数,无需自定义逻辑即可完成向前填充:

select 
  t1.A_NR, 
  t1.V_DATE, 
  t1.AMOUNT T1_AMOUNT,
  -- 按A_NR分组排序,自动取组内当前行之前最近的非空T2_AMOUNT值
  LAST_VALUE(t2.AMOUNT) IGNORE NULLS OVER (
    PARTITION BY t1.A_NR 
    ORDER BY t1.V_DATE 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS 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

SQL Server 2016及以上版本(低版本兼容方案)

如果数据库版本不支持IGNORE NULLS参数,可通过累计标记分组块的方式实现空值填充,核心逻辑是为每个非空的T2_AMOUNT值生成一个连续的块标记,块内所有空值共享同一个非空值:

WITH base_join AS (
  -- 先完成原有左连接查询
  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
),
block_calc AS (
  -- 为每个非空值累计计数,生成块标记
  SELECT 
    *,
    SUM(CASE WHEN T2_AMOUNT IS NOT NULL THEN 1 ELSE 0 END)
      OVER (PARTITION BY A_NR ORDER BY V_DATE) AS value_block
  FROM base_join
)
-- 按A_NR、块标记分组,取每个块内的非空值填充所有空行
SELECT 
  A_NR,
  V_DATE,
  T1_AMOUNT,
  MAX(T2_AMOUNT) OVER (PARTITION BY A_NR, value_block) AS T2_AMOUNT
FROM block_calc
ORDER BY A_NR, V_DATE

注:该兼容方案对所有支持窗口函数的SQL Server版本均适用,无特殊语法依赖。

内容的提问来源于stack exchange,提问作者Peter S

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.15 08:06:09