在 SQLSERVER2005以后，mssql开始有了递归查询的方法了。比较起最开始写存储过程或者写function的方式。这样的方式更加简便灵活的。
而oracle也有自带的树形结构递归查询方法，connect by
下面我自己写的一段SQL，简单注释下CTE共用表达式的一些用法。 实现对树状结构的根节点和子节点的查询。
代码 -- ---------------------------------------------------------------------- -- author:jc_liumangtu(【DBA】小七) -- date: 2010-03-30 15:09:42 -- version: -- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) -- Oct 14 2005 00:33:37 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- -- ---------------------------------------------------------------------- use test set nocount on if object_id ( ' Dept ' , ' U ' ) is not null drop table Dept go create table Dept(ID int ,ParentID int ,Name varchar ( 20 )) insert into Dept select 1 , 0 , ' AA ' insert into Dept select 2 , 1 , ' BB ' insert into Dept select 3 , 1 , ' CC ' insert into Dept select 4 , 2 , ' DD ' insert into Dept select 5 , 3 , ' EE ' insert into Dept select 6 , 0 , ' FF ' insert into Dept select 7 , 6 , ' GG ' insert into Dept select 8 , 7 , ' HH ' insert into Dept select 9 , 7 , ' II ' insert into Dept select 10 , 7 , ' JJ ' insert into Dept select 11 , 9 , ' KK ' go SELECT * FROM Dept; -- 查询树状结构某节点的上级所有根节点。 with cte_root(ID,ParentID,NAME) as ( -- 起始条件 select ID,ParentID,NAME from Dept where Name = ' II ' -- 列出子节点查询条件 union all -- 递归条件 select a.ID,a.ParentID,a.NAME from Dept a inner join cte_root b -- 执行递归，这里就要理解下了 on a.ID = b.ParentID -- 根据基础表条件查询子节点(a.ID)，通过CTE递归找到其父节点(b.ParentID)。 ) -- 可以和下面查询子节点的cte_child对比。 select * from cte_root ; -- 查询树状结构某节点下的所有子节点。 with cte_child(ID,ParentID,NAME) as ( -- 起始条件 select ID,ParentID,NAME from Dept where Name = ' II ' -- 列出父节点查询条件 union all -- 递归条件 select a.ID,a.ParentID,a.NAME from Dept a inner join cte_child b on ( a.ParentID = b.ID) -- 根据查询到的父节点(a.Parent)，通过CTE递归查询出其子节点(b.ID) ) select * from cte_child -- 可以改变之前的查询条件'II'再测试结果 ID ParentID Name -- --------- ----------- -------------------- 1 0 AA 2 1 BB 3 1 CC 4 2 DD 5 3 EE 6 0 FF 7 6 GG 8 7 HH 9 7 II 10 7 JJ 11 9 KK ID ParentID NAME -- --------- ----------- -------------------- 9 7 II 7 6 GG 6 0 FF ID ParentID NAME -- --------- ----------- -------------------- 9 7 II 11 9 KK
在msdn中介绍了CTE的一些限制：
至少有一个定位点成员和一个递归成员，当然，你可以定义多个定位点成员和递归成员，但所有定位点成员必须在递归成员的前面
定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符，最后一个定位点成员与递归成员之间必须使用UNION ALL，递归成员之间也必须使用UNION ALL连接 定位点成员和递归成员中的字段数量和类型必须完全一致 递归成员的FROM子句只能引用一次CTE对象 递归成员中不允许出现下列项 SELECT DISTINCT GROUP BY HAVING 标量聚合 TOP LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN） 子查询
接下来介绍下Oracle里面的递归查询方法，connect by prior ，start with。相对于SqlServer来说，Oracle的方法更加简洁明了，简单易懂。很容易就让人理解其用法。借来我会用和上面SqlServer同样的数据和结构进行代码演示，和对一些关键字的用法进行阐述。
SELECT …..
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH]；
下面是代码测试：
代码 -- 创建表 create table Dept(ID int ,ParentID int ,Name varchar ( 20 )); -- 增加测试数据，和上面的SqlServer数据相同 insert into Dept select 1 , 0 , ' AA ' from dual; insert into Dept select 2 , 1 , ' BB ' from dual; insert into Dept select 3 , 1 , ' CC ' from dual; insert into Dept select 4 , 2 , ' DD ' from dual; insert into Dept select 5 , 3 , ' EE ' from dual; insert into Dept select 6 , 0 , ' FF ' from dual; insert into Dept select 7 , 6 , ' GG ' from dual; insert into Dept select 8 , 7 , ' HH ' from dual; insert into Dept select 9 , 7 , ' II ' from dual; insert into Dept select 10 , 7 , ' JJ ' from dual; insert into Dept select 11 , 9 , ' KK ' from dual; commit ; -- 查询根节点（父节点） select * from Dept -- 查询基础表 connect by id = prior parentid -- connect by就是字段的关联关键字，prior有预先和前的意思，则是放在哪个字段前，哪个就是递归的上一层 start with name = ' II ' ; -- start with则是递归的起始位置，也可以用id或者是parentid。可以修改II的值测试其他数据。 -- 查询结果 ID PARENTID NAME 9 7 II 7 6 GG 6 0 FF -- 查询子节点 select * from Dept connect by prior id = parentid -- 同样的语句，仅仅改变prior位子，就发生了指向性的变化，就是这里id为递归上一层。 start with name = ' II ' ; -- 查询结果 ID PARENTID NAME 9 7 II 11 9 KK -- 测试结果和SqlServer一致，语句却更精练，简洁易懂。
经过分别对SqlServer和Oracle的测试，发现两个数据库都很好的支持递归查询，相比之下Oracle的递归查询语句更加简练易懂，更容易让人理解。
在做测试的时候，SqlServer更方便的产生测试数据，上面的代码可以复制后重复执行，而Oracle复制执行一次可以，重复执行的话，在执行创建表的工作，就会报错了，原因很简单，Oracle要判断表存在然后删除后重建的工作用代码实现很麻烦。而SqlServer只需要if后drop表再create就搞定。所以两种数据库各有千秋。
