oracle 树结构查询双向递归查询语句

小鱼儿 2024-04-19 13:33 30阅读 0赞

在项目中使用z-tree这种形式的插件时，往往表结构也是树杈型，及一个表中既包含自己的id，又包含父级id，可以无限递归下去，这种结构使用起来非常方便，易于拓展，但是同时带来节点递归查询的问题，幸运的是oracle提供了此类查询的标准sql公式，记录一下，以便使用！

### 公式 ###

SELECT LEVEL,t.*　　
    FROM 表名 T 
    --这里的level是指根据下面指定的方向的层次，本体为第一级
    [WHERE LEVEL= '2']
    --开始查询的条件，一般为某条记录的id=？
    START WITH T.ORG_ID = 28
    -- 这里会向紧跟着PRIOR后面的第一个参数的方向查询，可以控制向后查询子集和向前查询父集
    -- id = 父级id，向下查询
    --CONNECT BY PRIOR T.ORG_ID = T.F_ID 
    -- 父级id = 本条id 向上查询
    CONNECT BY PRIOR 父级id = 本条记录id
    --　WHERE 条件3
    ORDER　BY　LEVEL;

### 数据库语句 ###

*  创建测试用户 ***test1024***

-- Create the user 
    create user test1024
    identified by test1024
    default tablespace USERS
    temporary tablespace TEMP;
    -- Grant/Revoke role privileges 
    grant connect to test1024;
    grant resource to test1024;
    -- Set the user's default roles 
    alter user test1024
    default role connect, resource;

*  创建表

-- Create table
    create table org
    (
      id   number not null,
      name varchar2(50) not null,
      fid  number not null
    )
    ;
    -- Add comments to the columns 
    comment on column org.id
      is 'id';
    comment on column org.name
      is '名称';
    comment on column org.fid
      is '父级id';

*  插入数据

insert into ORG (ID, NAME, FID)
    values (1, '中国', 0);
    
    insert into ORG (ID, NAME, FID)
    values (2, '江苏', 1);
    
    insert into ORG (ID, NAME, FID)
    values (3, '徐州', 2);
    
    insert into ORG (ID, NAME, FID)
    values (4, '南京', 2);
    
    insert into ORG (ID, NAME, FID)
    values (5, '上海', 1);
    
    insert into ORG (ID, NAME, FID)
    values (6, '雨花台区', 4);
    
    insert into ORG (ID, NAME, FID)
    values (7, '泉山区', 3);
    
    insert into ORG (ID, NAME, FID)
    values (8, '日本', 0);
    
    insert into ORG (ID, NAME, FID)
    values (9, '东京', 8);
    
    insert into ORG (ID, NAME, FID)
    values (10, '名古屋', 8);
    
    insert into ORG (ID, NAME, FID)
    values (11, '大阪', 8);
    
    insert into ORG (ID, NAME, FID)
    values (12, '大田区', 9);

![在这里插入图片描述][20190919091756360.png]

### 示例 ###

###### 表 org ######

*  向上级查询所有上级

SELECT LEVEL,t.*
    FROM org t
    START WITH T.ID = 12
    -- 这里会向紧跟着PRIOR后面的第一个参数的方向查询，可以控制向后查询子集和向前查询父集
    CONNECT BY PRIOR T.FID = T.ID
    --　WHERE 条件3
    ORDER　BY　LEVEL;

![在这里插入图片描述][20190919091939164.png]

*  向下级查询所有下级

SELECT LEVEL,t.*
    FROM org t
    START WITH T.ID = 1
    -- 这里会向紧跟着PRIOR后面的第一个参数的方向查询，可以控制向后查询子集和向前查询父集
    CONNECT BY PRIOR T.ID = T.FID
    --　WHERE 条件3
    ORDER　BY　LEVEL;

![在这里插入图片描述][20190919092025690.png]

[20190919091756360.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/55f47f683a9a420daf39519574ead435.png
[20190919091939164.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/9d1c92b6d61a41da8c69e1a686cd820a.png
[20190919092025690.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/07ea0c38604c45b4982d534102e7682b.png