在 SQLSERVER2005以后,mssql开始有了递归查询的方法了。比较起最开始写存储过程或者写function的方式。这样的方式更加简便灵活的。
而oracle也有自带的树形结构递归查询方法,connect by
下面我自己写的一段SQL,简单注释下CTE共用表达式的一些用法。 实现对树状结构的根节点和子节点的查询。
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
-- 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];
下面是代码测试:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
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一致,语句却更精练,简洁易懂。