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

王朝学院·作者佚名  2016-05-26
窄屏简体版  字體: 小  |  中  |  大  |  超大  

根据上一行填充本行的空白栏位,SQL处理方式我在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的行填上成绩呢?

首先我们先建立示例表:

1createtablet12(3IDintidentityPRimarykey,4Scoreintnull5);6insertt17values(3),(4),(null),(3),(null),(null),(5);89select*10fromt1

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

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

1witht2as3(4select*fromt1whereScoreisnotnull5unionall6selectt1.ID,t.Score7fromt8innerjoint19ont.ID+1=t1.ID10wheret1.Scoreisnull11)12select*13fromt14orderbyID;得到的结果为:

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

delete fromt1whereID=5

这个时候如果还是运行上面的CTE就会查不到ID=6的记录,因为inner join的条件不成立了。那么简单的办法就是使用开窗函数给每一行数据增加一列连续自增的列,SQL Server中的函数是ROW_NUMBER().这样就变成了两个CTE嵌套使用,请看代码:1witht1new2as3(4select*,ROW_NUMBER()over(orderbyID)asRowNo5fromt16)7,t8as9(10selectId,Score,RowNofromt1newwhereScoreisnotnull11unionall12selectt1new.ID,t.Score,t1new.RowNo13fromt14innerjoint1new15ont.RowNo+1=t1new.RowNo16wheret1new.Scoreisnull17)1819select*20fromt21orderbyID公用表表达式真的很强大,另外在使用View出Report的时候,也可以用CTE,因为在View中不能用临时表,所以使用CTE代替临时表是个不错的解决方案。

 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
© 2005- 王朝網路 版權所有 導航