SQL递归查询（SqlServer/ORACLE递归查询）[语法差异分析]
在 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就搞定。所以两种数据库各有千秋。
希望这段测试能对各位看官有所帮助，不懂的可以留言解答。
-------------------------------------------------------------------------华丽的分割线------------------------------------------------------------------------------------
