SQL：如何用前一行值填充空单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3465847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How to fill empty cells with previous row value?
提问by Faiz
I need to produce the column "required" in the following table using SQL without using loops and correlated sub queries. Is this possible in SQL 2008?
我需要使用 SQL 生成下表中的“必需”列，而不使用循环和相关子查询。这在 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
Basically I am filling empty "Value" cells with last know value for that customer. Remember the last row may not have a valid value, so you will have to pick it from the row before that with a valid value.
基本上，我正在用该客户的最后知道的价值填充空的“价值”单元格。请记住，最后一行可能没有有效值，因此您必须从具有有效值的前一行中选择它。
采纳答案by sql_williamd
Faiz,
法兹，
how about the following query, it does what you want as far as I understand it. The comments explain each step. Take a look at CTEs on Books Online. This example could even be changed to use the new MERGE command for SQL 2008.
以下查询如何，据我所知，它可以满足您的需求。注释解释了每个步骤。看看在线书籍上的 CTE。此示例甚至可以更改为使用 SQL 2008 的新 MERGE 命令。
/* 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 @Customers
回答by feetwet
This is the "Last non-null puzzle," and here's one of several elegant solutions:
这是“最后一个非空谜题”，这是几个优雅的解决方案之一：
If your "sparse" table is SparseTablewith columns Date, Customer, Value then:
如果您的“稀疏”表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
Where Valuecouldn't be filled forward it will still be NULL, so you can then
凡Value不能充满着它仍然是NULL，这样你就可以再
update FullTable set Required = 0 where Required is null
回答by TomTom
I need to produce the column "required" in the following table using SQL without using loops and correlated sub queries. Is this possible in SQL 2008?
我需要使用 SQL 生成下表中的“必需”列，而不使用循环和相关子查询。这在 SQL 2008 中可能吗？
Impossible. Point. Not possibly on ANY SQL based server, including oracle.
不可能的。观点。不可能在任何基于 SQL 的服务器上，包括 oracle。
The main problem here is that you rule out loops AND correlated subqueries and any way to retrieve the value at query time will ultimately use another query to find the valid value (actually one per field). This is how SQL works. Yes, you could hide them in a custom scalar function, but still they would contain a logical sub query.
这里的主要问题是您排除了循环和相关子查询，并且在查询时检索值的任何方法最终都将使用另一个查询来查找有效值（实际上每个字段一个）。这就是 SQL 的工作方式。是的，您可以将它们隐藏在自定义标量函数中，但它们仍将包含逻辑子查询。
回答by Lieven Keersmaekers
I'm not sure if following counts considering your constraints but it gets the job done.
我不确定考虑到您的限制，以下是否重要，但它可以完成工作。
Test data
测试数据
DECLARE @Customers TABLE (Date DATETIME, Customer INTEGER, Value INTEGER)
INSERT INTO @Customers VALUES ('20100101', 1, 12 )
INSERT INTO @Customers VALUES ('20100101', 2, NULL)
INSERT INTO @Customers VALUES ('20100101', 3, 32 )
INSERT INTO @Customers VALUES ('20100101', 4, 42 )
INSERT INTO @Customers VALUES ('20100101', 5, 15 )
INSERT INTO @Customers VALUES ('20100102', 1, NULL)
INSERT INTO @Customers VALUES ('20100102', 2, NULL)
INSERT INTO @Customers VALUES ('20100102', 3, 39 )
INSERT INTO @Customers VALUES ('20100102', 4, NULL)
INSERT INTO @Customers VALUES ('20100102', 5, 16 )
INSERT INTO @Customers VALUES ('20100103', 1, 13 )
INSERT INTO @Customers VALUES ('20100103', 2, 24 )
INSERT INTO @Customers VALUES ('20100103', 3, NULL)
INSERT INTO @Customers VALUES ('20100103', 4, NULL)
INSERT INTO @Customers VALUES ('20100103', 5, 21 )
INSERT INTO @Customers VALUES ('20100104', 1, 14 )
INSERT INTO @Customers VALUES ('20100104', 2, NULL)
INSERT INTO @Customers VALUES ('20100104', 3, NULL)
INSERT INTO @Customers VALUES ('20100104', 4, 65 )
INSERT INTO @Customers VALUES ('20100104', 5, 23 )
Query
询问
SELECT c.Date
, c.Customer
, Value = COALESCE(c.Value, cprevious.Value, 0)
FROM @Customers c
INNER JOIN (
SELECT c.Date
, c.Customer
, MaxDate = MAX(cdates.Date)
FROM @Customers c
LEFT OUTER JOIN (
SELECT Date
, Customer
FROM @Customers
) cdates ON cdates.Date < c.Date AND cdates.Customer = c.Customer
GROUP BY
c.Date, c.Customer
) cmax ON cmax.Date = c.Date AND cmax.Customer = c.Customer
LEFT OUTER JOIN @Customers cprevious ON cprevious.Date = cmax.MaxDate AND cprevious.Customer = cmax.Customer
ORDER BY
1, 2, 3
Update statement
更新声明
UPDATE @Customers
SET Value = c2.Value
OUTPUT Inserted.*
FROM @Customers c
INNER JOIN (
SELECT c.Date
, c.Customer
, Value = COALESCE(c.Value, cprevious.Value, 0)
FROM @Customers c
INNER JOIN (
SELECT c.Date
, c.Customer
, MaxDate = MAX(cdates.Date)
FROM @Customers c
LEFT OUTER JOIN (
SELECT Date
, Customer
FROM @Customers
) cdates ON cdates.Date < c.Date AND cdates.Customer = c.Customer
GROUP BY
c.Date, c.Customer
) cmax ON cmax.Date = c.Date AND cmax.Customer = c.Customer
LEFT OUTER JOIN @Customers cprevious ON cprevious.Date = cmax.MaxDate AND cprevious.Customer = cmax.Customer
) c2 ON c2.Date = c.Date
AND c2.Customer = c.Customer
回答by pritaeas
How about a left outer join on the same table where the date is less than the current one and the value is non-empty, ordered by date desc (limit 1), returning zero when null ? (No server available to test at the moment). Unless this counts as a sub-query...
同一个表上的左外连接如何，其中日期小于当前值且值为非空，按日期 desc（限制 1）排序，当为 null 时返回零？（目前没有可供测试的服务器）。除非这算作子查询......
