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的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。