sqlserver 递归查询,oraclesql递归查询讲解

　　SQLSERVER2005之后，mssql开始有了递归查询方法。比较一开始编写存储过程或函数的方式。这种方式更加方便灵活。

　　Oracle也有自己的树结构递归查询方法，connect by。

　　下面是我自己写的一段SQL，简单记录了CTE共享表达式的一些用法。查询树结构的根节点和子节点。

　　代码。

　　-作者：JC _ liumangtu ([DBA]小七)

　　-日期：2010年3月30日15时09分42秒

　　-版本：

　　-微软SQL Server 2005 - 9.00.1399.06(英特尔X86)

　　-2005年10月14日00:33:37

　　Windows NT 5.1上的开发人员版(内部版本2600: Service Pack 3)

　　使用测试

　　设置nocount on

　　如果object_id(Dept ， U )不为空

　　删除表格部门

　　去

　　创建表Dept(ID int，ParentID int，Name varchar(20))

　　插入到部门选择1，0， AA

　　插入到部门选择2，1， BB

　　插入到部门选择3，1，抄送

　　插入到部门选择4，2， DD

　　插入到部门选择5，3， EE

　　插入到部门选择6，0， FF

　　插入到部门选择7，6， GG

　　插入到部门选择8，7， HH

　　插入到部门选择9，7， II

　　插入到部门选择10，7， JJ

　　插入到部门选择11，9， KK

　　去

　　SELECT * FROM部门；

　　-查询树结构中一个节点的所有根节点。

　　with cte_root(ID，ParentID，NAME)

　　如同

　　（

　　-起始条件

　　选择ID，ParentID，名称

　　自部门

　　其中= ii -列出子节点的查询条件

　　联合所有

　　-递归条件

　　选择一个ID，一个ParentID，一个NAME

　　来自部门a

　　内部连接

　　cte _ rootb-执行递归，这里就明白了

　　On a.ID=b.ParentID -根据基本表条件查询子节点(a.ID)，通过CTE递归查找其父节点(b.ParentID)。

　　)-可以和下面查询子节点的cte_child进行比较。

　　select * from cte _ root

　　-查询树结构中某个节点下的所有子节点。

　　with cte_child(ID，ParentID，NAME)

　　如同

　　（

　　-起始条件

　　选择ID，ParentID，名称

　　自部门

　　其中= ii -列出父节点的查询条件

　　联合所有

　　-递归条件

　　选择一个ID，一个ParentID，一个NAME

　　来自部门a

　　内部连接

　　cte _子b

　　on(a.Parent ID=b.ID)-根据查询的父节点(a . Parent)，通过CTE递归查询其子节点(b . ID)。

　　)

　　Select * from cte_child -您可以更改之前的查询条件‘II’并重新测试结果。

　　ID ParentID名称

　　- - -

　　1 0 AA

　　2 1 BB

　　3 1毫升

　　4月2日

　　5 3 EE

　　6 0 FF

　　7 6 GG

　　8 7 HH

　　9 7二

　　10月7日JJ

　　11月9日KK

　　ID ParentID名称

　　- - -

　　9 7二

　　7 6 GG

　　6 0 FF

　　ID ParentID名称

　　- - -

　　9 7二

　　11月9日KK

　　复制代码

　　msdn中介绍了CTE的一些限制：

　　至少有一个定位点成员和一个递归成员。当然，您可以定义多个定位点成员和递归成员，但是所有定位点成员必须在递归成员之前。

　　UNION ALL、UNION、INTERSECT、EXCEPT集合运算符必须在定位点成员之间使用，UNION ALL必须在最后一个定位点成员和递归成员之间使用，UNION ALL必须用于连接递归成员。

　　点成员和递归成员中字段的数量和类型必须完全相同。

　　递归成员的FROM子句只能引用一次CTE对象。

　　递归成员中不允许有下列项目

　　选择不同

　　分组依据

　　拥有

　　标量聚合

　　顶端

　　左、右、外联接(允许内联接)

　　子查询

　　接下来介绍Oracle中的递归查询方法，connect by prior，start with。与SqlServer相比，Oracle的方法更加简洁易懂。人们很容易理解它的用法。借用一下，我会用和上面SqlServer一样的数据和结构来演示代码，并解释一些关键字的用法。

　　选择….

　　By {前一列名1=列名2列名1=前一列名2}

　　[开始于]；

　　下面是代码测试：

　　代码-创建表

　　创建表Dept(ID int，ParentID int，Name varchar(20))；

　　-添加测试数据，与上面的SqlServer数据相同。

　　插入到部门选择1，0，从dual中选择‘AA’；

　　插入到部门选择2，1，从dual中选择 BB ；

　　插入到部门选择3，1，从双重中选择“抄送”；

　　插入到部门选择4，2，从dual中选择 DD ;

　　插入到部门选择5，3，从dual中选择‘EE’；

　　插入到Dept select 6，0，从dual中选择FF；

　　插入到部门选择7，6，从dual中选择‘GG’；

　　插入到部门从dual中选择8，7， HH ；

　　插入到部门选择9，7，从dual中选择‘II’；

　　插入到部门选择10，7，从dual中选择“JJ”；

　　插入到部门选择11，9，从dual中选择“KK”；

　　提交；

　　-查询根节点(父节点)

　　Select * from Dept -查询基本表

　　Connect by ID=prior parent ID-Connect by是字段的关联关键字。Prior的意思是“之前”和“之前”，哪个字段放在前面，哪个递归更高。

　　以name=II 开头；-startwith是递归的开始位置。也可以使用id或parentid。可以修改II的值来测试其他数据。

　　-查询结果

　　ID PARENTID名称

　　9 7二

　　7 6 GG

　　6 0 FF

　　-查询子节点

　　select * from部门

　　by prior id=parentid——同样的语句，只是改变了先验位置，改变了指向性，也就是这里的ID是递归的。

　　以name=II 开头；

　　-查询结果

　　ID PARENTID名称

　　9 7二

　　11月9日KK

　　-测试结果与SqlServer一致，但语句更简洁、凝练、易懂。复制代码

　　分别对SqlServer和Oracle进行测试后，发现两个数据库都很好地支持递归查询，相比之下Oracle的递归查询语句更加简洁易懂。

　　测试时，SqlServer可以更方便地生成测试数据，上面的代码可以被复制和重复执行。但是Oracle可以复制执行一次，但是如果重复执行，在执行创建表的工作时会报错。原因很简单。Oracle判断一个表是否存在，然后删除，用代码重建，非常麻烦。而SqlServer只需要在if之后删除表，然后再创建它。所以这两个数据库各有千秋。

sqlserver 递归查询,oraclesql递归查询讲解