SQL:如何用以前的行值填充空单元格？
SQL:如何用以前的行值填充空单元格？
提问于 2010-08-12 16:16:57
我需要在下表中使用SQL生成"required“列，而不使用循环和相关子查询。在SQL 2008中这是可能的吗？
Date Customer Value Required Rule
20100101 1 12 12
20100101 2 0 If no value assign 0
20100101 3 32 32
20100101 4 42 42
20100101 5 15 15
20100102 1 12 Take last known value
20100102 2 0 Take last known value
20100102 3 39 39
20100102 4 42 Take last known value
20100102 5 16 16
20100103 1 13 13
20100103 2 24 24
20100103 3 39 Take last known value
20100103 4 42 Take last known value
20100103 5 21 21
20100104 1 14 14
20100104 2 24 Take last known value
20100104 3 39 Take last known value
20100104 4 65 65
20100104 5 23 23基本上，我是用该客户最后知道的值填充空的" value“单元格。请记住，最后一行可能没有有效值，因此您必须从具有有效值的前一行中选取它。
回答 5
Stack Overflow用户
回答已采纳
发布于 2010-08-13 15:29:06
法伊兹
下面的查询如何，据我所知，它做了你想做的事情。注释解释了每一步。请查看联机丛书上的CTE。此示例甚至可以更改为使用SQL2008的新合并命令。
/* Test Data & Table */
DECLARE @Customers TABLE
(Dates datetime,
Customer integer,
Value integer)
INSERT INTO @Customers
VALUES ('20100101', 1, 12),
('20100101', 2, NULL),
('20100101', 3, 32),
('20100101', 4, 42),
('20100101', 5, 15),
('20100102', 1, NULL),
('20100102', 2, NULL),
('20100102', 3, 39),
('20100102', 4, NULL),
('20100102', 5, 16),
('20100103', 1, 13),
('20100103', 2, 24),
('20100103', 3, NULL),
('20100103', 4, NULL),
('20100103', 5, 21),
('20100104', 1, 14),
('20100104', 2, NULL),
('20100104', 3, NULL),
('20100104', 4, 65),
('20100104', 5, 23) ;
/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH CustCTE
AS (SELECT Customer,
Value,
Dates,
ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
FROM @Customers),
/* CleanCust - A recursive CTE. This runs down the list of values for each customer, checking the Value column, if it is null it gets the previous non NULL value.*/
CleanCust
AS (SELECT Customer,
ISNULL(Value, 0) Value, /* Ensure we start with no NULL values for each customer */
Dates,
RowNum
FROM CustCte cur
WHERE RowNum = 1
UNION ALL
SELECT Curr.Customer,
ISNULL(Curr.Value, prev.Value) Value,
Curr.Dates,
Curr.RowNum
FROM CustCte curr
INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
AND curr.RowNum = prev.RowNum + 1)
/* Update the base table using the result set from the recursive CTE */
UPDATE trg
SET Value = src.Value
FROM @Customers trg
INNER JOIN CleanCust src ON trg.Customer = src.Customer
AND trg.Dates = src.Dates
/* Display the results */
SELECT * FROM @CustomersStack Overflow用户
发布于 2017-12-12 22:31:05
这就是"Last non-null puzzle“，这是几个优雅的解决方案之一：
如果您的“稀疏”表是包含Date、Customer、Value列的SparseTable，那么：
with C as
(select *,
max(case when Value is not null then [Date] end)
over (partition by Customer order by [Date] rows unbounded preceding) as grp
from SparseTable
)
insert into FullTable
select *,
max(Value) over (partition by Customer, grp order by [Date] rows unbounded preceding) as Required
from C在Value不能向前填充的地方，它仍然是NULL，所以你可以
update FullTable set Required = 0 where Required is nullStack Overflow用户
发布于 2010-08-12 16:30:23
我需要使用SQL在下表中生成"required“列，而不使用循环和相关子查询。在SQL 2008中这是可能的吗？
不可能。点。不可能在任何基于SQL的服务器上，包括oracle。
这里的主要问题是您排除了循环和相关子查询，任何在查询时检索值的方法最终都将使用另一个查询来查找有效值(实际上是每个字段一个)。这就是SQL的工作方式。是的，您可以将它们隐藏在自定义标量函数中，但它们仍然包含一个逻辑子查询。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3465847
复制相关文章
相似问题
腾讯云开发者
