basic_table
id | pid | name |
1 | 小红 | |
2 | 1 | 小黄 |
3 | 1 | 小绿 |
4 | 1 | 小蓝 |
5 | 小紫 | |
6 | 5 | 小青 |
7 | 5 | 小黑 |
8 | 5 | 小白 |
9 | 8 | 小小 |
1.简单的树结构查询
select * from basic_table connect by prior id = p_id ; (从上往下查找)
select * from basic_table connect by id = prior p_id;(从下往上查找)
2.带初始条件的查询
select * from basic_table start with id = 1 connect by prior id = p_id;(只会返回id=1的这棵树)
3.带结果条件的查询
select * from basic_table where id = 3 start with id = 1 connect by prior id = p_id;(只会返回id=3的这一个节点)
4.子节点和顶级节点的查询
select connect_by_root p_id as rootId, id from basic_table connect by prior id= p_id (返回所有id与顶级节点的关系)