递归查询(树查询)

　　递归查询（树查询）在一般用于菜单/权限/分类等,在不同的数据库中有各自的查询方式. 本文介绍了在Oracle/sqlserver 两种数据库中树查询的语法结构.
　　
1. Oracle 树查询 使用语法connect by (prior) t.parent_id = (prior) t.id start with id = ??
prior 只有一个,在不同的位置查不同的结果.prior 在前查前辈,prior在后查后生

示例1 查询已知节点所有的父(祖)节点(查询id = 140408 和其所有的父菜单)

select t.id,t.parent_id,t.value 
  from sys_resource t 
  where t.resource_type = 'menu' 
  connect by prior t.parent_id = t.id start with id =140408;

结果

示例2 查询一个节点所有的子(孙)节点(查询id = 14 和其所有的子菜单)

select t.id,t.parent_id,t.value 
       from sys_resource t 
       where t.resource_type = 'menu' 
       connect by  t.parent_id =  prior t.id start with t.id = 14 
       order by t.parent_id;

结果

注意查父节点和子节点区别 prior 位置不同.(记忆:prior 在前查前辈,prior在后查后生)

2. SQL SERVER 树查询 sqlserver 树查询相对麻烦一些需要使用到CTE(Common Table Expression公用表表达式)

示例3 查所有父(祖)节点

with CTE_SYS_RESOURCE(ID,RESOURCE_TYPE,VALUE,PARENT_ID)  
as  
(  
    --起始条件  
    select ID,RESOURCE_TYPE,VALUE ,PARENT_ID
    from dbo.SYS_RESOURCE  
    where id = 30023   --列出父节点查询条件  

    union all  
    --递归条件  

    select a.ID,a.RESOURCE_TYPE,a.VALUE,a.PARENT_ID 
    from SYS_RESOURCE a  
    inner join   
    CTE_SYS_RESOURCE b          --执行递归   
    on a.id=b.PARENT_ID 
) 
select * from CTE_SYS_RESOURCE t; 

结果

示例4 查所有子(孙)节点

with CTE_SYS_RESOURCE(ID,RESOURCE_TYPE,VALUE,PARENT_ID) 
as  
(  
    --起始条件  
    select ID,RESOURCE_TYPE,VALUE ,PARENT_ID
    from dbo.SYS_RESOURCE  
    where id = 30000   --列出子节点查询条件  

    union all  

    --递归条件  
    select a.ID,a.RESOURCE_TYPE,a.VALUE,a.PARENT_ID 
    from SYS_RESOURCE a  
    inner join   
    CTE_SYS_RESOURCE b          --执行递归
    on a.PARENT_ID=b.id 
)                         
select * from CTE_SYS_RESOURCE t;  

结果

Oracle/sqlserver对CTE都是支持的.但是二者有些细微的差别. 示例3/示例4中的sql在Oracle中也可以正确运行,但是在sqlserver中,示例3可以简化成如下sql (CTE_SYS_RESOURCE 列名省略了)

WITH CTE_SYS_RESOURCE AS 
(
    SELECT T.ID,T.PARENT_ID,T.RESOURCE_TYPE,T.VALUE FROM dbo.SYS_RESOURCE T WHERE T.ID = 30023
    UNION ALL
    SELECT T1.ID,T1.PARENT_ID,T1.RESOURCE_TYPE,T1.VALUE FROM dbo.SYS_RESOURCE T1,CTE_SYS_RESOURCE T2 WHERE T1.ID = T2.PARENT_ID
)
SELECT * FROM CTE_SYS_RESOURCE ;

在sqlserver中,递归CTE 可以把列名省略,但是递归CTE在Oracle中不能省略列名. 当然普通的CTE在sqlserver/Oracle中均可省略列名.

CTE 的介绍可以参考
CTE 说明1
CTE 说明2

Mysql 树查询暂未了解,后继补上.