【SQL SERVER小品】跟踪追击 -- SQL SERVER递归查询示例

todaynew

　　递归，是遍历具有层次结构数据的一种算法，各种程序语言中均具有递归运算的方法，在SQL SERVER和ORACLE中也具有递归查询的能力。在SQL SERVER中递归查询的写法有点怪异，其基本语法结构为：

WITH <CTE name> [(<column name>[,...n])]
AS
(<query returning tabular data>)
<statement that will make use of thn CTE>

　　比如，在单据数据库中有一个单据表，这个数据表用来存储单据的信息。其中包含单据ID、单据编号、父单ID等多个字段。单据之间存在的单据链，由单据ID、父单ID这两个字段来表达，也就是通过这两个字段形成单据之间的层次关系。依据这个数据表，我们可以写一个递归查询，来更明确的表示单据之间的关系。我们可以这样来写：



USE 单据数据库;
GO

WITH 单据递归查询(单据ID,单据编号,父单ID,根单据,level)
AS(        /*根记录*/
        SELECT 单据ID,单据编号,父单ID,单据ID AS 根单据,0 AS level
        FROM 单据表 WHERE ISNULL(父单ID,0)=0
        /*递归运算*/
        UNION ALL
        SELECT a.单据ID,a.单据编号,a.父单ID,b.根单据,level+1
        FROM 单据表 AS a,单据递归查询 AS b
        WHERE a.父单ID=b.单据ID
)
SELECT * FROM 单据递归查询 ORDER BY 根单据,level;

　　WITH子句后面是递归查询的名称（可以随意取），然后是递归查询需要返回的字段列表。其中level是节点的等级，这个等级在根记录上应该设置为0，递归运算时逐步加1。level的取值方法，在AS子句中不难看出。根单据这个字段，不是必要的。但设计这个字段，有利于对记录的分组，也就是可以通过排序容易的找到同一个根单据下的所有各层级子单据。

　　AS子句用于构成递归查询的主体语句，它看起来是一个联合查询。其中第一个SELECT查询出所有的根记录。然后通过UNION配合第二个SELECT进行递归运算，并联合起来。联合的条件存在于WHERE子句中。需要注意的是SQL SERVER递归的层次最多100层，超过这个限制将会跳错。

　　最后的一个SELECT子句是用来返回结果集的。整个语法有点像创建一个单语句表值函数，由WITH创建一个临时表变量，由AS给表变量追加记录，然后由最后的SELECT返回这个表变量的结果集。

　　当然，我们也可以持久性的保存这样的递归查询，方法是将递归查询创建为视图。下面是我们用单据数据库中的BOM表创建一个递归视图的示例：

laimf

学习了

zpy2

好贴 赞一个

风中漫步

收藏了.谢谢

5988143

拜讀學習一下~

zhuyiwen

赞一个！不过打开这个网页太慢了。