我有一种情况,我的表有2列,一个列有一系列日期,另一列有一些值。现在,我需要使用与最新日期相对应的值更新空值,如下所示。
我不能像SQL Server 2008R2那样使用LEAD和LAG函数。
示例数据在下面。
DECLARE @Table TABLE
(
Date_D DATE,
Val INT
);
INSERT INTO @Table
(
Date_D,
Val
)
VALUES
('2019-06-15', 2),
('2019-06-16', NULL),
('2019-06-17', NULL),
('2019-06-18', 7),
('2019-06-19', 1),
('2019-06-20', 5),
('2019-06-21', NULL),
('2019-06-22', NULL),
('2019-06-23', NULL),
('2019-06-24', NULL),
('2019-06-25', 9),
('2019-06-26', 5),
('2019-06-27', 3),
('2019-06-28', 4),
('2019-06-29', NULL),
('2019-06-30', 1)
SELECT * FROM @Table
你可以做
SELECT Date_D, CASE WHEN Val IS NULL THEN
(
SELECT TOP 1 Val
FROM @Table
WHERE Val IS NOT NULL AND Date_D < T.Date_D
ORDER BY Date_D DESC
) ELSE Val END
FROM @Table T;
如果要更新表,则将其与结果结合在一起。
为了 UPDATE
;WITH CTE AS
(
SELECT Date_D, CASE WHEN Val IS NULL THEN
(
SELECT TOP 1 Val
FROM @Table
WHERE Val IS NOT NULL AND Date_D < T.Date_D
ORDER BY Date_D DESC
) ELSE Val END Val
FROM @Table T
)
UPDATE T
SET T.Val = CTE.Val
FROM @Table T INNER JOIN CTE
ON T.Date_D = CTE.Date_D
WHERE T.Val IS NULL; --You can also AND Date_D BETWEEN StartDate AND EndDate
看看它如何进行现场演示
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句