从前一行的非空值更新空列值

本文介绍了从前一行的非空值更新空列值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

@@版本 1

使用 SQL Server 2008，我试图将值级联到一列.我有一个包含组 id (GID) 和 Seq 的表，其中包含组内记录的排序.对于存在的列，在这种情况下 Name 和 Salary - 我的真实表有超过 50 列，如果它们包含 NULL，我需要使用包含非空值的该列的前一行的值更新 NULL 值.

Using SQL Server 2008, I am trying to cascade values down a column. I have a table with group id (GID) and Seq containing ordering for records within the group. For the columns present, in this case Name and Salary - my real table has over 50 columns, if they contain NULLs I need to update the NULL value with the value from the previous row for that column that contain a non-null value.

这里有一些东西可以说明这一点:

Here is something to illustrate this:

GID Seq Name Salary 1 1 James NULL 1 2 NULL 100 1 3 NULL NULL 2 1 NULL 81 2 2 Smith NULL 2 3 NULL NULL 3 1 Charles NULL 3 2 NULL NULL 3 3 Brown NULL 3 4 NULL 75 4 0 Ron 50 4 1 NULL 20 4 2 NULL NULL

我的结果应该是:

GID Seq Name Salary 1 1 James NULL 1 2 James 100 1 3 James 100 2 1 NULL 81 2 2 Smith 81 2 3 Smith 81 3 1 Charles NULL 3 2 Charles NULL 3 3 Brown NULL 3 4 Brown 75 4 0 Ron 50 4 1 Ron 20 4 2 Ron 20

我希望在不使用动态 SQL、循环或游标的情况下执行此操作.

I am looking to do this without using dynamic SQL, loops or cursors.

简单测试用例代码:

DECLARE @Test TABLE (GID int, Seq int, Name varchar(50), Salary decimal) INSERT INTO @Test VALUES (1, 1, 'James', NULL) INSERT INTO @Test VALUES (1, 2, NULL, 100.40) INSERT INTO @Test VALUES (1, 3, NULL, NULL) INSERT INTO @Test VALUES (2, 1, NULL, 80.50) INSERT INTO @Test VALUES (2, 2, 'Smith', NULL) INSERT INTO @Test VALUES (2, 3, NULL, NULL) INSERT INTO @Test VALUES (3, 1, 'Charles', NULL) INSERT INTO @Test VALUES (3, 2, NULL, NULL) INSERT INTO @Test VALUES (3, 3, 'Brown', NULL) INSERT INTO @Test VALUES (3, 4, NULL, 75) INSERT INTO @Test VALUES (4, 0, 'Ron', 50) INSERT INTO @Test VALUES (4, 1, NULL, 20) INSERT INTO @Test VALUES (4, 2, NULL, NULL) SELECT * FROM @Test

@@版本 2感谢 GilM 对@@Version 1 的解决方案.我对这个问题做了一点补充.Seq 列中的起始数字可能是 0 或 1.在第一个问题的解决方案中，递归 CTE 中的锚点指的是 1，如果它是 1 或 0 呢?本版本以上三个代码块都添加了最后3行数据(GID=4).

@@Version 2 Thanks GilM for the solution to @@Version 1. I have made a small addition to the problem. The starting number in the Seq column may be either a 0 or 1. In the solution to the first problem the anchor in the recursive CTE refers to 1, what if its either a 1 or 0? The last 3 rows of data (GID = 4) were added to all the above three code blocks in this version.

谢谢！

推荐答案

这个怎么样?:

;WITH CTE AS ( SELECT GID, SEQ, Name, Salary FROM @Test t1 WHERE SEQ = (SELECT MIN(SEQ) FROM @Test t2 WHERE t2.GID = t1.GID) UNION ALL SELECT t.GID, t.SEQ, COALESCE(t.Name,c.Name), COALESCE(t.Salary,c.Salary) FROM CTE c JOIN @Test t ON t.GID = c.GID AND t.SEQ = c.SEQ+1 ) UPDATE t SET Name = c.Name, Salary = c.Salary FROM @Test t JOIN CTE c ON c.GID = t.GID AND c.Seq = t.SEQ