oracle查询重复数据集中所有空值的最后一个已知值

UPDATE yourTable t1
SET value = (SELECT value
             FROM   (SELECT value, ROWNUM AS rn
                     FROM yourTable t2
                     WHERE t2.datetime < t1.datetime AND t2.value IS NOT NULL
                     ORDER BY datetime DESC)
             WHERE  rn = 1)
WHERE value IS NULL;

value 哪一个不是 NULL .

如果您只想选择当前表来生成预期的输出,可以尝试:

SELECT
    datetime,
    CASE WHEN value IS NULL
         THEN (SELECT value
               FROM   (SELECT value, ROWNUM AS rn
                       FROM yourTable t2
                       WHERE t2.datetime < t1.datetime AND t2.value IS NOT NULL
                       ORDER BY datetime DESC)
               WHERE  rn = 1)
         ELSE value END AS value
FROM yourTable t1;

我通过以下查询获得了所需的结果:

  SELECT DATETIME, VALUE, last_value(VALUE) ignore nulls over (order by DATETIME) 
  FROM
  TABLE1;