SQL递归查询（SqlServer/ORACLE递归查询）[语法差异分析]

-- ----------------------------------------------------------------------
-- 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

-- 创建表
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一致，语句却更精练，简洁易懂。