如果行为空，则从前一行获取值。

Question

如果行为空，则从前一行获取值。

3

我有这个透视表

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

我发现了这篇文章，它可能会对我有帮助，但它只能操作一列。如何将其应用于所有列，或者由于我的列是动态的，我该如何实现这样的结果。

任何帮助都将不胜感激。谢谢！

- Boy Pasmo

2

我猜在透视之前这样做会更容易。 - James Z

是的。但我无法找到任何确定哪些列是“NULL”的方法。 - Boy Pasmo

这些行在原始数据中不存在（假设您实际上没有 NULL 值）。 - James Z

有可能确定哪些行不存在吗？举个例子会是一个很好的参考。 - Boy Pasmo

这取决于你的数据，但例如http://stackoverflow.com/questions/25038494/sql-server-show-rows-for-missing-dates 上有一些想法。你也可以考虑创建一个日历表（每天1行），通常有助于与日历有关的查询。 - James Z

@JamesZ 我会尝试一下。谢谢你的建议。 - Boy Pasmo

3个回答

0

我想给你建议一个解决方案。如果你有一个只包含两列的表格，我的解决方案会完美地工作。

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

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

- Evgenii Petukhov

0

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

- Abhishek Rana

1

您的回答可以通过提供更多支持信息来改进。请编辑以添加进一步细节，例如引用或文档，以便他人可以确认您的答案是正确的。您可以在帮助中心找到有关如何撰写好答案的更多信息。 - Community

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

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创建一个索引。