递归查询(树查询)

　　递归查询（树查询）在一般用于菜单/权限/分类等,在不同的数据库中有各自的查询方式. 本文介绍了在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 树查询暂未了解,后继补上.