树形结构算法.doc

树形结构算法：SQL desc ttree;Name Type Nullable Default Comments - - - - - ID NUMBER(5) Y TITLE VARCHAR2(20) Y PARENT NUMBER Y SQL select * from ttree; ID TITLE PARENT- - - 1 Library 0 2 News 1 3 World News 2 4 Politics 2 5 Business 2 6 Science 2 7 Technology 2 8 Sports 1 9 Local 8 10 Collegiate 8 11 Professional 8 12 Soccer 9 13 Soccer 10 14 Soccer 11 15 Football 9 16 Football 10 17 Football 11 18 aa 3 19 bb 18 20 cc 19 ID TITLE PARENT- - - 21 df 0 22 sdf 0 23 sdf 46 24 sdf 21 25 sdf 22 26 sdf 19 27 asdf 22 28 asdf 21 30 asdf 19 31 asdf 24 32 asdf 31 33 asdf 32 34 asdf 33 35 asdf 34 36 asdf 35 37 asdf 36 38 asdf 37 39 asdf 36 40 asdf 38 41 asdf 25 42 asdf 26 ID TITLE PARENT- - - 43 asdf 22 44 asdf 23 45 asdf 22 46 asdf 23 47 asdf 2246 rows selected通过以下语句得到结果：SQL select lpad(id,level+length(id)-1,*) test 2 from test_tree start with 3 parent=0 connect by prior id=parent；TEST-1*2*3*18*19*20*26*42*30*4*5*6*7*8*9*12*15*10*13*16TEST-*11*14*1721*24*31*32*33*34*35*36*37*38*40*39*2822*25*41*27*43TEST-*45*4743 rows selected.树形结构表的遍历写法： 代码: select .from . start with . connect by prior(父子关系表达式)3.家族树语法:select column from table_name start with column=valueconnect by prior 父主键=子主键3.1排除单一性和分枝以ORACLE中的EMP表为例例从顶到底列出各雇员的信息SQL select lpad( ,4*(level-1)|ename name,empno,mgr from emp start with mgr is null 2 connect by prior empno=mgr;NAME EMPNO MGR- - -KING 7839 JONES 7566 7839 SCOTT 7788 7566 ADAMS 7876 77883.2遍历至根例1现在要从某个雇员开始向他的上级列出该雇员的层次结构SQL col ename for a30;SQL select lpad( ,4*(level-1)|ename ename,mgr,empno from scott.emp 2 start with mgr=7788 connect by prior mgr=empno;ENAME MGR EMPNO- - -ADAMS 7788 7876 SCOTT 7566 7788 JONES 7839 7566 KING 7839例2列出所有雇员的层次结构SQL select lpad( ,4*(level-1)|ename ename,empno,mgr from scott.emp 2 start with mgr is not null 3 connect by empno=prior mgr;ENAME EMPNO MGR- - -SMITH 7369 7902 FORD 7902 7566 JONES 7566 7839 KING 7839ALLEN 7499 7698 BLAKE 7698 7839 KING 7839WARD 7521 7698 BLAKE 7698 7839 KING 7839JONES 7566 7839 KING 7839MARTIN 7654 7698 BL