SQL QUERY 用先前已知值中的值替换一行中的 NULL 值 [英] SQL QUERY replace NULL value in a row with a value from the previous known value

问题描述

我有两列

date   number       
----   ------
1      3           
2      NULL        
3      5           
4      NULL        
5      NULL        
6      2          
.......

我需要用新值替换 NULL 值采用日期列中上一个日期的最后一个已知值的值eg: date=2 number = 3, date 4 and 5 number = 5 and 5. NULL 值随机出现.

I need to replace the NULL values with new values takes on the value from the last known value in the previous date in the date column eg: date=2 number = 3, date 4 and 5 number = 5 and 5. The NULL values appear randomly.

推荐答案

如果您使用的是 Sql Server，这应该可以工作

If you are using Sql Server this should work

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 3
INSERT INTO @Table (ID,Val) SELECT 2, NULL
INSERT INTO @Table (ID,Val) SELECT 3, 5
INSERT INTO @Table (ID,Val) SELECT 4, NULL
INSERT INTO @Table (ID,Val) SELECT 5, NULL
INSERT INTO @Table (ID,Val) SELECT 6, 2


SELECT  *,
        ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM    @Table t

这篇关于SQL QUERY 用先前已知值中的值替换一行中的 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！