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“单元格。请记住，最后一行可能没有有效值，因此您必须从具有有效值的前一行中选取它。
Stack Overflow用户
发布于 2010-08-12 16:49:36
考虑到你的限制，我不确定下面的内容是否算数，但它能完成工作。
测试数据
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 )查询
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 @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 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3465847
复制相关文章
相似问题
腾讯云开发者
