java 查询父节点数据添加集合 oracle 查询父节点

oracle 树查询----select...start with....connect by....prior

1. 创建表

create table test 
(
    id integer not null,
    var varchar2(20),
    par_id integer,  ------父节点
    level integer    ------树的级别
);
create table test 
(
    id integer not null,
    var varchar2(20),
    par_id integer,  ------父节点
    level integer    ------树的级别
);

tips: 顶级父节点的par_id 建议设置为0；

1. 查询操作

1. 查询所有顶级父节点 sql select * from test where par_id = 0;
2. 查询儿子节点

select * from test where par_id = :1;
select * from test where par_id = :1;

1. 查询某节点所有后代

select * from test start with id = :1 connect by par_id = prior id;
select * from test start with id = :1 connect by par_id = prior id;

1. 查找一个节点的直属父节点（父亲）

SELECT b.* FROM test a JOIN test b ON a.par_id =  WHERE  = :1;
SELECT b.* FROM test a JOIN test b ON a.par_id =  WHERE  = :1;

1. 查找一个节点的所有直属父节点（祖宗)

SELECT * FROM test START WITH id = :1 CONNECT BY PRIOR par_id = id;
SELECT * FROM test START WITH id = :1 CONNECT BY PRIOR par_id = id;

1. 查询一个节点的兄弟节点（亲兄弟）

select a.* from test a where exists(select * from test b where a.par_id = b.par_id and  = :1);
select a.* from test a where exists(select * from test b where a.par_id = b.par_id and  = :1);

1. 查询与一个节点同级的节点（族兄弟）。 如果在表中设置了级别的字段，上表中的level，那么在做这类查询时会很轻松，同一级别的就是与那个节点同级的，在这里列出不使用该字段时的实现!

with tmp as 
(select a.*, level lev from test a 
start with a.par_id = 0 connect by a.par_id = prior )
select * from tmp where lev = (select lev from tmp where id = :1);
with tmp as 
(select a.*, level lev from test a 
start with a.par_id = 0 connect by a.par_id = prior )
select * from tmp where lev = (select lev from tmp where id = :1);

1. 更深层查询

-- 创建表
CREATE TABLE flfl
(
     id     NUMBER NOT NULL,
     mc     NVARCHAR2(20),
     fljb   NUMBER,
     sjflid NUMBER
)； 
-- 创建表
CREATE TABLE flfl
(
     id     NUMBER NOT NULL,
     mc     NVARCHAR2(20),
     fljb   NUMBER,
     sjflid NUMBER
)；

• 查询一个节点的父节点的的兄弟节点（伯父与叔父）

WITH tmp
     AS (SELECT flfl.*,
                LEVEL lev
         FROM   flfl
         START WITH sjflid IS NULL
         CONNECT BY sjflid = PRIOR id) SELECT b.*
FROM   tmp b,
       (SELECT *
        FROM   tmp
        WHERE  id = 7004
               AND lev = 2) a
WHERE  b.lev = 1
UNION ALL
SELECT *
FROM   tmp
WHERE  sjflid = (SELECT DISTINCT 
                 FROM   tmp x,
                        tmp y,
                        (SELECT *
                         FROM   tmp
                         WHERE  id = 7004
                                AND lev > 2) z
                 WHERE   = z.sjflid
                        AND  = y.sjflid);
WITH tmp
     AS (SELECT flfl.*,
                LEVEL lev
         FROM   flfl
         START WITH sjflid IS NULL
         CONNECT BY sjflid = PRIOR id) SELECT b.*
FROM   tmp b,
       (SELECT *
        FROM   tmp
        WHERE  id = 7004
               AND lev = 2) a
WHERE  b.lev = 1
UNION ALL
SELECT *
FROM   tmp
WHERE  sjflid = (SELECT DISTINCT 
                 FROM   tmp x,
                        tmp y,
                        (SELECT *
                         FROM   tmp
                         WHERE  id = 7004
                                AND lev > 2) z
                 WHERE   = z.sjflid
                        AND  = y.sjflid);

这里查询分成以下几步。首先，将第7个一样，将全表都使用临时表加上级别；其次，根据级别来判断有几种类型，以上文中举的例子来说，有三种情况：（1）当前节点为顶级节点，即查询出来的lev值为1，那么它没有上级节点，不予考虑。（2）当前节点为2级节点，查询出来的lev值为2，那么就只要保证lev级别为1的就是其上级节点的兄弟节点。（3）其它情况就是3以及以上级别，那么就要选查询出来其上级的上级节点（祖父），再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。 最后，就是使用UNION将查询出来的结果进行结合起来，形成结果集。

• 查询一个节点的父节点的同级节点（族叔）

WITH tmp
     AS (SELECT a.*,
                LEVEL lev
         FROM   flfl a
         START WITH a.sjflid IS NULL
         CONNECT BY a.sjflid = PRIOR )
SELECT *
FROM   tmp
WHERE  lev = (SELECT lev
              FROM   tmp
              WHERE  id = 819394) - 1
WITH tmp
     AS (SELECT a.*,
                LEVEL lev
         FROM   flfl a
         START WITH a.sjflid IS NULL
         CONNECT BY a.sjflid = PRIOR )
SELECT *
FROM   tmp
WHERE  lev = (SELECT lev
              FROM   tmp
              WHERE  id = 819394) - 1

• 名称要列出名称全部路径。
  这里常见的有两种情况，一种是是从顶级列出，直到当前节点的名称（或者其它属性）；一种是从当前节点列出，直到顶级节点的名称（或其它属性）。举地址为例：国内的习惯是从省开始、到市、到县、到居委会的，而国外的习惯正好相反。

从顶部开始：

SELECT Sys_connect_by_path (mc, '/')
FROM   flfl
WHERE  id = 6498
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR id;
SELECT Sys_connect_by_path (mc, '/')
FROM   flfl
WHERE  id = 6498
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR id;

从当前节点开始：

SELECT Sys_connect_by_path (mc, '/')
FROM   flfl
START WITH id = 6498
CONNECT BY PRIOR sjflid = id;  
SELECT Sys_connect_by_path (mc, '/')
FROM   flfl
START WITH id = 6498
CONNECT BY PRIOR sjflid = id;

sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串，这个功能还是很强大的。

• 列出当前节点的根节点。

SELECT connect_by_root mc,
       flfl.*
FROM   flfl
START WITH id = 6498
CONNECT BY PRIOR sjflid = id; 
SELECT connect_by_root mc,
       flfl.*
FROM   flfl
START WITH id = 6498
CONNECT BY PRIOR sjflid = id;

connect_by_root函数用来列的前面，记录的是当前节点的根节点的内容。

• 列出当前节点是否为叶子。

SELECT connect_by_isleaf,
       flfl.*
FROM   flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR id; 
SELECT connect_by_isleaf,
       flfl.*
FROM   flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR id;

connect_by_isleaf函数用来判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，这里返回0；反之，如果不包含下级节点，这里返回1