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

  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

  -版权所有(c) 1988-2005微软公司

  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之后删除表,然后再创建它。所以这两个数据库各有千秋。

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: