根据上一行填充本行的空白栏位，SQL处理方式（递归方式）

原文出处

https://www.cnblogs.com/studyzy/p/4244606.html

情况 Excel中导入数据的时候，有些数据行可能为空，需要用上一行非空的数据进行填充。

为了方便说明，我举了一个简单的例子，假设一个学生成绩表，有字段“学生ID”和“成绩”，学生ID是主键，自增，成绩只有NULL和1，2，3，4，5这几个值。在录入学生成绩的时候，如果成绩为NULL，就表示该学生成绩和上一个学生的成绩相同。现在要查询某个学生ID的成绩，该怎么查呢？或者要将成绩字段改为不允许为空，怎么把所有NULL的行填上成绩呢？

首先我们先建立示例表：

create table t2
  (
  ID int  primary key,
 Score int 
  );
  
insert all into t2
values
  (1, 3) into t2
values
  (2, 4) into t2
values
  (3, null) into t2
values
  (4, 3) into t2
values
  (5, null) into t2
values
  (6, null) into t2
values
  (7, 5)
select 1 from dual;

结果

ID	SCORE
1	3
2	4
3
4	3
5
6
7	5

 

从结果我们可以看到如果要查询学生6的成绩，那么应该先去查学生5的成绩，由于学生5也是空，所以要继续查前一个学生4的成绩，得到分数3，所以学生6的成绩是3.这显然是一个递归问题，如果一直是空，会继续递归下去，直到找到一个成绩为止。要在SQL中使用递归，那么第一个应该想到的就是公用表表达式CTE。关于CTE的语法和说明可以看MSDN：https://msdn.microsoft.com/zh-cn/library/ms186243.aspx

那么我们这里递归的终点是什么呢？是不为空的成绩，递归的链接条件是上一个学生ID=当前学生ID-1.于是我们可以将此次的公用表表达式写为：

with t(id,score)
as
(
select * from t2 where Score is not null
union all
select t2.ID,t.Score
from t
inner join t2
on t.ID+1=t2.ID
where t2.Score is null
)
select *
from t
order by ID;

 

得到的结果为：

这里的情况比较特殊ID是连续的，那么如果ID不连续会怎么样呢？我们试着删除ID=5

delete from t1 where ID=5

这个时候如果还是运行上面的CTE就会查不到ID=6的记录，因为inner join的条件不成立了。那么简单的办法就是使用开窗函数给每一行数据增加一列连续自增的列，SQL Server中的函数是ROW_NUMBER().这样就变成了两个CTE嵌套使用，请看代码：

with t1new(id,
Score,
RowNo) as
 (select th.*, ROW_NUMBER() over(order by ID) as RowNo from tt th),
t(id,
Score,
RowNo) as
 (select Id, Score, RowNo
    from t1new
   where Score is not null
  union all
  select t1new.ID, t.Score, t1new.RowNo
    from t
   inner join t1new
      on t.RowNo + 1 = t1new.RowNo
   where t1new.Score is null)
select * from t order by ID

 

公用表表达式真的很强大，另外在使用View出Report的时候，也可以用CTE，因为在View中不能用临时表，所以使用CTE代替临时表是个不错的解决方案。