1、准备演示数据
创建表结构:
-- Create table createtable@H_403_25@ Z_ORG( cid NUMBER@H_403_25@,cname VARCHAR2@H_403_25@(32@H_403_25@),parent_id NUMBER@H_403_25@,create_time DATE,@H_403_25@org_level NUMBER@H_403_25@) tablespace@H_403_25@ POWERDESK @H_403_25@ pctfree10 @H_403_25@ initrans1 @H_403_25@ maxtrans255@H_403_25@; -- Add comments to the table commentontable@H_403_25@ Z_ORG is'机构组织简化表'@H_403_25@; -- Add comments to the columns commentoncolumn@H_403_25@ Z_ORG.cid is'主键ID'@H_403_25@; commentoncolumn@H_403_25@ Z_ORG.cname is'组织名称'@H_403_25@; commentoncolumn@H_403_25@ Z_ORG.parent_id is'上级组织ID'@H_403_25@; commentoncolumn@H_403_25@ Z_ORG.create_time is'创建时间'@H_403_25@; commentoncolumn@H_403_25@ Z_ORG.org_level is'组织级别'@H_403_25@; |
录入数据:
@H_403_25@ insertinto@H_403_25@ z_org(cid,cname,parent_id,create_time,org_level) select1@H_403_25@,'地球'@H_403_25@,0@H_403_25@,sysdate@H_403_25@,1from@H_403_25@ dual; @H_403_25@ insertinto@H_403_25@ z_org(cid,org_level) select2@H_403_25@,'中国'@H_403_25@,1@H_403_25@,2from@H_403_25@ dual; @H_403_25@ insertinto@H_403_25@ z_org(cid,org_level) select3@H_403_25@,'上海直辖市'@H_403_25@,2@H_403_25@,3from@H_403_25@ dual; @H_403_25@ insertinto@H_403_25@ z_org(cid,org_level) select4@H_403_25@,'江苏省'@H_403_25@,org_level) select5@H_403_25@,'南京市'@H_403_25@,4@H_403_25@,4from@H_403_25@ dual; @H_403_25@ insertinto@H_403_25@ z_org(cid,org_level) select6@H_403_25@,'苏州市'@H_403_25@,org_level) select7@H_403_25@,'无锡市'@H_403_25@,org_level) select8@H_403_25@,'虹口区'@H_403_25@,3@H_403_25@,org_level) select9@H_403_25@,'浙江省'@H_403_25@,org_level) select10@H_403_25@,'杭州市'@H_403_25@,9@H_403_25@,4from@H_403_25@ dual; @H_403_25@ insertinto@H_403_25@ z_org(cid,org_level) select11@H_403_25@,'宁波市'@H_403_25@,org_level) select12@H_403_25@,'美国'@H_403_25@,org_level) select13@H_403_25@,'加利福尼亚州'@H_403_25@,12@H_403_25@,org_level) select14@H_403_25@,'旧金山市'@H_403_25@,13@H_403_25@,org_level) select15@H_403_25@,'撒门市'@H_403_25@,4from@H_403_25@ dual; commit@H_403_25@; |
2、遍历根节点
1. selectcode1fromtablename
2. startwithcode2
3. connectbycode3
4. wherecond3
code2是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
code3是连接条件,其中用prior表示上一条记录,比如connect by prior id=parentid就是说上一条记录的id是本条记录的parent,即本记录的父亲是上一条记录。
code4是过滤条件,用于对返回的所有记录进行过滤。
prior和start with关键字是可选项:
prior运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,prior运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。start with子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询条件的行作为根节点。
遍历表,求得某一个节点的所有上级节点记录,比如求得撒门市的上级组织,应该获得加利福尼亚州,美国,地球这些记录,如下所示:
sql@H_403_25@> select@H_403_25@ t.cid,t.cname,t.parent_id,level @H_403_25@ 2@H_403_25@ from@H_403_25@ z_org t @H_403_25@ 3@H_403_25@ startwith@H_403_25@ t.cname='撒门市' @H_403_25@ 4@H_403_25@ connectbynocycleprior@H_403_25@ t.parent_id=t.cid @H_403_25@ 5@H_403_25@ orderbylevelasc @H_403_25@ CID CNAME PARENT_ID LEVEL ---------- -------------------------------- ---------- ---------- @H_403_25@ 15@H_403_25@撒门市@H_403_25@ 13@H_403_25@ 1 @H_403_25@ 13@H_403_25@加利福尼亚州@H_403_25@ 12@H_403_25@ 2 @H_403_25@ 12@H_403_25@美国@H_403_25@ 1@H_403_25@ 3 @H_403_25@ 1@H_403_25@地球@H_403_25@ 0@H_403_25@ 4 @H_403_25@ |
遍历求得根路径字符串:
sql@H_403_25@> select@H_403_25@ cname_child,wm_concat(t2.cname) @H_403_25@ 2@H_403_25@ from@H_403_25@( @H_403_25@ 3@H_403_25@ select'苏州市'@H_403_25@ cname_child,t.cid,level @H_403_25@ 4@H_403_25@ from@H_403_25@ z_org t @H_403_25@ 5@H_403_25@ startwith@H_403_25@ t.cname in@H_403_25@('苏州市'@H_403_25@) @H_403_25@ 6@H_403_25@ connectbynocycleprior@H_403_25@ t.parent_id=t.cid @H_403_25@ 7@H_403_25@ )t2 groupby@H_403_25@ cname_child ; @H_403_25@CNAME_CHILD WM_CONCAT(T2.CNAME) ----------- -------------------------------------------------------------------------------- @H_403_25@苏州市@H_403_25@ @H_403_25@苏州市@H_403_25@,@H_403_25@地球@H_403_25@,@H_403_25@中国@H_403_25@,@H_403_25@江苏省 @H_403_25@ |
3、遍历子节点
5. selectcode1fromtablename
6. startwithcode2
7. connectbycode3
8. wherecond3
code2是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
code3是连接条件,其中用prior表示上一条记录,比如connect by prior id=parentid就是说上一条记录的id是本条记录的parent,即本记录的父亲是上一条记录。
code4是过滤条件,用于对返回的所有记录进行过滤。
prior和start with关键字是可选项:
prior运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,prior运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。
start with子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询条件的行作为根节点。
如下所示,prior偏向父节点,就往下查询,查询节点下面所有的子节点记录:
select@H_403_25@ t1.parent_id,t1.cid,casewhenlevel@H_403_25@=1then'*'@H_403_25@||t1.cname whenlevel@H_403_25@=2then'*-----'@H_403_25@||t1.cname whenlevel@H_403_25@=3then'*-----*-----'@H_403_25@||t1.cname else@H_403_25@ t1.cname end@H_403_25@,level from@H_403_25@ z_org t1 startwith@H_403_25@ t1.parent_id = 1 connectby@H_403_25@ t1.cid=prior@H_403_25@ t1.parent_id @H_403_25@; |
如下所示E:\u\oracle\problem\pic\52.png:
如下所示,prior偏向子节点,就往下查询,查询节点下面所有的子节点记录:
select@H_403_25@ t1.parent_id,level from@H_403_25@ z_org t1 startwith@H_403_25@ t1.parent_id = 1 connectbyprior@H_403_25@ t1.cid=t1.parent_id @H_403_25@; |
执行结果如下所示E:\u\oracle\problem\pic\51.png:
4、扩展研究
---从parentid到cid开始递归,并以为主展示这条记录
select t1.parentid,level
from test_category t1
start with t1.parentid = 1
connect by prior t1.parentid = t1.cid
;
---从parentid到cid开始递归,并以parentid为主展示这条记录
select t1.parentid,level
from test_category t1
start with t1.cid = 1
connect by prior t1.parentid = t1.cid
;
---从parentid到cid开始递归,并以cid为主展示这条记录
select t1.cid,t1.parentid,level
from test_category t1
start with t1.parentid = 1
connect by t1.parentid = prior t1.cid
;
---从parentid到cid开始递归,并以cid为主展示这条记录select t1.cid,levelfrom test_category t1start with t1.cid = 1connect by t1.parentid = prior t1.cid;