Oracle的树状查询

桃扇骨 2022-12-31 15:19 78阅读 0赞

Oracle的树状查询语句很简单SELECT 。。。START WITH condition CONNECT BY condition;

Oracle使用树状结构的目的是：

1.用二维表的形式记录树状结构  
2.查询二维表来构造树状结构，一般都是根据一个对象向上查询查出它所有的祖先，根据一个对象向下查询查出它所有的子孙

树状结构的特点就是一个子只有一个父，一个父可以有多个子。那只要每一个对象知道了他的父对象，就可以手动的构造出树状结构。也就是说，有多少个对象在二维表中就需要多少行，每一个对象都有一个指出它的父对象是字段。  
看图中所示，二维表应该表示为  
对象                      父对象  
![36ce50b849aebf8e4b5cd6a270f12ce7.png][]

以下是创建表的命令

SQL> create table tree_t (id char,p_id char);
    
    Table created.
    
    SQL> insert into tree_t values('A',NULl);
    
    1 row created.
    
    SQL> insert into tree_t values('B','A');
    
    1 row created.
    
    SQL> insert into tree_t values('C','A');
    
    1 row created.
    
    SQL> insert into tree_t values('D','A');
    
    1 row created.
    
    SQL> insert into tree_t values('E','B');
    
    1 row created.
    
    SQL> insert into tree_t values('F','B');
    
    1 row created.
    
    SQL> insert into tree_t values('G','C');
    
    1 row created.
    
    SQL> insert into tree_t values('H','D');
    
    1 row created.
    
    SQL> insert into tree_t values('I','F');
    
    1 row created.
    
    SQL> insert into tree_t values('J','H');
    
    1 row created.
    
    SQL> insert into tree_t values('K','H');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.

SQL> select * from tree_t;

![89dc06257b1c5b35278ff88adcbc355f.png][]

那怎样根据二维表中的记录查询一个对象的祖先和子孙呢，这就要用到SELECT 。。。START WITH condition CONNECT BY condition命令了

1.先要确定查询哪个对象的祖先和子孙  
这要用到START WITH condition，只要满足条件的行，都可以做为查询的对象，也就是说，在一个查询中可以查询多个对象的祖先和子孙

2.再确定对象的字段和父对象的字段，向上查询还是向下查询  
CONNECT BY prior id = p\_id        对象字段为id，父对象字段为p\_id，向下查询  
CONNECT BY id = prior p\_id        对象字段为id，父对象字段为p\_id，向上查询

下面是举例  
查询ID = D的子孙

SQL> select * from tree_t start with id = 'D' connect by prior id = p_id;

![3f44b4ee2faa5135757e9f90a7f5f1d3.png][]

查询ID = D祖先

SQL> select * from tree_t start with id = 'D' connect by  id = prior p_id;

![2a9bc8248c4e33fd21d310d3d29078e6.png][]

查询P\_ID = B的行的对象的祖先，这就是查询多个对象，因为P\_ID = B的对象有两个，E和F，查询E和F的祖先

SQL> select * from tree_t start with p_id = 'B' connect by  id = prior p_id;

![9b3690488316c07f386ab6a3a4ff97f0.png][]

[36ce50b849aebf8e4b5cd6a270f12ce7.png]: /images/20221120/b08cc32b79b942a69460827c4fd4351f.png
[89dc06257b1c5b35278ff88adcbc355f.png]: /images/20221120/c651ea7bd2cf4fd1aef097a839b758c3.png
[3f44b4ee2faa5135757e9f90a7f5f1d3.png]: /images/20221120/ecd9cc55b9124a10b4a5fffe37ae7d3d.png
[2a9bc8248c4e33fd21d310d3d29078e6.png]: /images/20221120/b048afbce3c0438994afea52f6a7f8ae.png
[9b3690488316c07f386ab6a3a4ff97f0.png]: /images/20221120/22019eeff85e41b88811ac9b87028c33.png