T-SQL查询更新空值 [英] T-SQL query update null values

问题描述

我在T-SQL中有一个非常具体的问题。

I have a very specific problem in T-SQL.

如果我能解决此示例问题，我给你，我想我将能够解决我的原始问题。

If I can solve this example case I give you I think I will be able to solve my original case.

在表中具有此数据：

DECLARE @Test TABLE
(
    Value INT
    ,Date DATETIME2(7)
);

INSERT INTO @Test
VALUES
(NULL, '2011-01-01 10:00'),
(NULL, '2011-01-01 11:00'),
(2, '2011-01-01 12:00'),
(NULL, '2011-01-01 13:00'),
(3, '2011-01-01 14:00'),
(NULL, '2011-01-01 15:00'),
(NULL, '2011-01-01 16:00'),
(4, '2011-01-01 17:00'),
(NULL, '2011-01-01 18:00'),
(5, '2011-01-01 19:00'),
(6, '2011-01-01 20:00')

我需要选择以下输出：

Value   Date
2       2011-01-01 10:00
2       2011-01-01 11:00
2       2011-01-01 12:00
2       2011-01-01 13:00
3       2011-01-01 14:00
3       2011-01-01 15:00
3       2011-01-01 16:00
4       2011-01-01 17:00
4       2011-01-01 18:00
5       2011-01-01 19:00
6       2011-01-01 20:00

给出一些解释。如果value在某些地方为NULL，则需要更新前一个小时的值。如果一行中有多个空值，则最接近的具有非空值的小时传播并填充所有这些空值。同样，如果一天中的第一个小时为空，则在这种情况下，一天中具有非空值的最早小时将像2一样向下传播。在您的情况下，您可以假定至少一个值是非空值。

To give some explanation. If value is NULL somewhere I need to update with the value from the previous hour. If there are several null values in a row the closest earlier hour with a non null value propagates and fills all these null values. Also if the first hour of the day is null then the earliest hour on the day with a non null value propagates downwards like 2 in this case. In your case you can assume that at least one value is non null value.

我的目标是使用Common表表达式或类似的方法来解决这个问题。通过光标的方式，我认为如果尝试的话，我会在短时间内找到解决方案，但是到目前为止，我对CTE和递归CTE的尝试都失败了。

My ambition is to solve this with Common table expressions or something similar. With the cursor way I think I would have the solution in short bit of time if I try but my attempts with CTEs and recursive CTEs have failed so far.

推荐答案

由于您的情况并不总是相同，所以这会更加困难。在您的示例中，前两行需要从第一个值中获取具有较晚日期的值，在其他情况下，它们需要从前一个日期中获取值。如果始终需要查看以前的日期，则可以简单地执行以下查询：

Since your condition is not always the same this is a little bit more difficult. In your example, the first two rows need to get their values from the first value with a later date, in the other cases they need to get the values from previous dates. If you would always need to look previous dates, you could simple do this query:

SELECT  B.Value,
        A.[Date]
FROM @Test A
OUTER APPLY (SELECT TOP 1 *
             FROM @Test
             WHERE [Date] <= A.[Date] AND Value IS NOT NULL
             ORDER BY [Date] DESC) B

但是在您的情况下，我认为而是您需要这个：

But in your case, I think that you need this instead:

SELECT  ISNULL(B.Value,C.Value) Value,
        A.[Date]
FROM @Test A
OUTER APPLY (SELECT TOP 1 *
             FROM @Test
             WHERE [Date] <= A.[Date] AND Value IS NOT NULL
             ORDER BY [Date] DESC) B
OUTER APPLY (SELECT TOP 1 *
             FROM @Test
             WHERE Value IS NOT NULL
             ORDER BY [Date]) C

这篇关于T-SQL查询更新空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！