如果row为NULL，则从前一行获取值

Question

我有这个透视表

+---------+----------+----------+-----+----------+
| Date    | Product1 | Product2 | ... | ProductN |
+---------+----------+----------+-----+----------+
| 7/1/15  | 5        | 2        | ... | 7        |
| 8/1/15  | 7        | 1        | ... | 9        |
| 9/1/15  | NULL     | 7        | ... | NULL     |
| 10/1/15 | 8        | NULL     | ... | NULL     |
| 11/1/15 | NULL     | NULL     | ... | NULL     |
+---------+----------+----------+-----+----------+

我想用上面的值填充NULL列。因此，输出应该是这样的:

+---------+----------+----------+-----+----------+
| Date    | Product1 | Product2 | ... | ProductN |
+---------+----------+----------+-----+----------+
| 7/1/15  | 5        | 2        | ... | 7        |
| 8/1/15  | 7        | 1        | ... | 9        |
| 9/1/15  | 7        | 7        | ... | 9        |
| 10/1/15 | 8        | 7        | ... | 9        |
| 11/1/15 | 8        | 7        | ... | 9        |
+---------+----------+----------+-----+----------+

我发现这篇文章可能对我有帮助，但这只操作一个列。我如何将此应用于我的所有列，或者我如何实现这样的结果，因为我的列是动态的。

任何帮助都将非常感激。谢谢!

Answer 1

ANSI标准在LAG()上有IGNORE NULLS选项。这就是你想要的。唉，SQL Server还没有实现这个特性。

你可以用几种方法来实现。一种是使用多个outer apply。另一个使用关联子查询:

select p.date,
       (case when p.product1 is not null else p.product1
             else (select top 1 p2.product1 from pivoted p2 where p2.date < p.date order by p2.date desc)
        end) as product1,
       (case when p.product1 is not null else p.product1
             else (select top 1 p2.product1 from pivoted p2 where p2.date < p.date order by p2.date desc)
        end) as product1,
       (case when p.product2 is not null else p.product2
             else (select top 1 p2.product2 from pivoted p2 where p2.date < p.date order by p2.date desc)
        end) as product2,
       . . .
from pivoted p ;

对于这个查询，我建议在date上建立索引。

Answer 2

我想给你一个解决方案。如果你有一个只有两列的表，我的解决方案将完美地工作。

+---------+----------+
| Date    | Product  |
+---------+----------+
| 7/1/15  | 5        |
| 8/1/15  | 7        |
| 9/1/15  | NULL     |
| 10/1/15 | 8        |
| 11/1/15 | NULL     |
+---------+----------+
select  x.[Date], 
        case
            when x.[Product] is null
            then min(c.[Product])
        else
            x.[Product]
        end as Product
from
(
    -- this subquery evaluates a minimum distance to the rows where Product column contains a value
    select  [Date], 
            [Product], 
            min(case when delta >= 0 then delta else null end) delta_min,
            max(case when delta < 0 then delta else null end) delta_max
    from
    (
        -- this subquery maps Product table to itself and evaluates the difference between the dates
        select  p.[Date],
                p.[Product], 
                DATEDIFF(dd, p.[Date], pnn.[Date]) delta
        from @products p
        cross join (select * from @products where [Product] is not null) pnn
    ) x
    group by [Date], [Product]
) x
left join @products c on x.[Date] = 
    case
        when abs(delta_min) < abs(delta_max) then DATEADD(dd, -delta_min, c.[Date]) 
        else DATEADD(dd, -delta_max, c.[Date])
    end
group by x.[Date], x.[Product]
order by x.[Date]

在这个查询中，我通过CROSS JOIN语句将表映射到包含值的自身行。然后我计算日期之间的差异，以便选择最接近的日期，然后用值填充空单元格。

结果:

+---------+----------+
| Date    | Product  |
+---------+----------+
| 7/1/15  | 5        |
| 8/1/15  | 7        |
| 9/1/15  | 7        |
| 10/1/15 | 8        |
| 11/1/15 | 8        |
+---------+----------+

实际上，建议查询不选择前一个值。相反，它选择最接近的值。换句话说，我的代码可以用于许多不同的目的。

Answer 3

First You need to add identity column in temporary or hard table then resolved by following method.
--- Solution ----
Create Table #Test (ID Int Identity (1,1),[Date] Date , Product_1 INT )
Insert Into #Test ([Date], Product_1)
Values
('7/1/15',5)
,('8/1/15',7)
,('9/1/15',Null)
,('10/1/15',8)
,('11/1/15',Null)

Select ID , DATE , 
IIF ( Product_1 is null ,
(Select Product_1 from #TEST
Where ID = (Select Top 1 a.ID From #TEST a where a.Product_1 is not null and a.ID<b.ID
Order By a.ID desc)
),Product_1) Product_1
from #Test b

-- Solution End ---