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

2021-08-26 06:54:59

我在4年多前，写了一篇Excel处理空白Cell的文章，http://www.cnblogs.com/studyzy/archive/2010/04/07/1706203.html，其实在数据库中也会遇到这种情况。对于普通的OLTP系统来说，应该不会出现，主要是在做OLAP，导入外部数据源时，可能导入系统的就是带有空白记录的数据。

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

首先我们先建立示例表：

 1 create table t1
 2 (
 3 ID int identity primary key,
 4 Score int null
 5 );
 6 insert t1
 7 values(3),(4),(null),(3),(null),(null),(5);
 8 
 9 select *
10 from t1

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

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

 1 with t
 2 as
 3 (
 4 select * from t1 where Score is not null
 5 union all
 6 select t1.ID,t.Score
 7 from t
 8 inner join t1
 9 on t.ID+1=t1.ID
10 where t1.Score is null
11 )
12 select *
13 from t
14 order by ID;

得到的结果为：

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

delete from t1 where ID=5

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

 1 with t1new
 2 as
 3 (
 4 select *,ROW_NUMBER() over(order by ID) as RowNo
 5 from t1
 6 )
 7 , t
 8 as
 9 (
10 select Id,Score,RowNo from t1new where Score is not null
11 union all
12 select t1new.ID,t.Score,t1new.RowNo
13 from t
14 inner join t1new
15 on t.RowNo+1=t1new.RowNo
16 where t1new.Score is null
17 )
18 
19 select *
20 from t
21 order by ID

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

本文转自深蓝居博客园博客，原文链接：http://www.cnblogs.com/studyzy/p/4244606.html，如需转载请自行联系原作者

码农公寓

相关文章