oracle中进行简单树查询(递归查询) ，PRIOR、CONNECT_BY_ROOT的使用

select AD_DEPARTMENT_CODE, AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_NAME<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> from (select AD_DEPARTMENT_CODE, AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_NAME from AD_DEPT_ARCHITECTURE order by AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_CODE) start with AD_DEPARTMENT_CODE = '0' connect by prior AD_DEPARTMENT_CODE = AD_DEPARTMENT_UP_LEVEL; 对from 后边 的select 结果集合进行了排序，然后，start with 、connect by prior这些语句起了作用，按照刚才查询出来结果集的顺序， （1） 先找到AD_DEPARTMENT_CODE = '0'的作为第一条记录(A)，将其加入到要构建的树状结构中。 （2） 然后顺序从排完序的结果集中找出，第一条记录的头一个子结点(B)添加到树中。如第一个子结点还有它的子结点，就依次从刚才的查询出来的结果集中找到B的子节点C1、C2、C3。。。依次添加到树中。 （3） 。。。。 有了from 后的select结果集的排序，得到的树状结构更加有序。

生成媒体频道栏目父子关系树状关系 SELECT APR_CODE, APR_CODE_UP, NAMES FROM (SELECT A.MEDIA_CODE || '|' || A.CHANNEL_CODE || '|' || A.COLUMN_CODE AS APR_CODE, A.MEDIA_CODE_UP || '|' || A.CHANNEL_CODE_UP || '|' || A.COLUMN_CODE_UP AS APR_CODE_UP, DECODE((B.MEDIA_NAME || '-' || C.CHANNEL_CODE || '-' || D.COLUMN_NAME), '--', '产品关系', (B.MEDIA_NAME || '|' || C.CHANNEL_CODE || '|' || D.COLUMN_NAME)) NAMES FROM COLUMNS D, CHANNEL C, MEDIA B, AD_PRODUCT_RELATION A WHERE A.MEDIA_CODE = B.MEDIA_CODE(+) AND A.MEDIA_CODE = C.MEDIA_CODE(+) AND A.CHANNEL_CODE = C.CHANNEL_CODE(+) AND A.MEDIA_CODE = D.MEDIA_CODE(+) AND A.CHANNEL_CODE = D.CHANNEL_CODE(+) AND A.COLUMN_CODE = D.COLUMN_CODE(+)) START WITH APR_CODE = '0|0|0' CONNECT BY PRIOR APR_CODE = APR_CODE_UP 这里利用'-' 和'|'来进行区分，找出了树状结构的根 为了构建树， （1）先建立了树结构的根，字段这里都设为“0”或者“00” （2）然后再根据需要填充数据 （3）利用上边的oracle的 PRIOR，CONNECT_BY_ROOT 等进行层次查询 <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1025" style="WIDTH: 481.5pt; HEIGHT: 112.5pt" o:ole="" type="#_x0000_t75"><imagedata o:title="" src="file:///C:/DOCUME~1/vincent/LOCALS~1/Temp/msohtml1/01/clip_image001.png"></imagedata></shape>

递归查询时：由于数据类型不正确而导致 “ ORA-01436: CONNECT BY loop in user data” <shape id="_x0000_i1026" style="WIDTH: 481.5pt; HEIGHT: 268.5pt" o:ole="" type="#_x0000_t75"><imagedata o:title="" src="file:///C:/DOCUME~1/vincent/LOCALS~1/Temp/msohtml1/01/clip_image003.png"></imagedata></shape> 错误原因：在数据库表TRASFER中，TRASFER字段为number类型，而TAPE_NO字段为Varchar2类型。由于在进行父子关系判定中“TRANSFER = TAPE_NO”两者的字段类型不同而导致：ORA-01436 正确语句： SELECT TRANSFER, TAPE_NO, TRANSFER_NAME FROM (SELECT TRANSFER, TAPE_NO, TRANSFER_NAME FROM TRANSFER ORDER BY TAPE_NO, TRANSFER) START WITH TRANSFER = 0 CONNECT BY PRIOR TO_CHAR(TRANSFER) = TAPE_NO

参考一

PL/SQL基础：阶层查询 作者：itpub cxck<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><chsdate w:st="on" year="2007" month="5" day="8" islunardate="False" isrocdate="False">2007-05-08</chsdate> 内容导航： ORACLE <chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="10" unitname="g">10g</chmetcnv>新增了阶层查询操作符PRIOR，CONNECT_BY_ROOT ■PRIOR 阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点， 作为运算符，PRIOR和加(+)减(-)运算的优先级相同。 ■阶层查询 语法：START WITH condition CONNECT BY NOCYCLE condition START WITH 指定阶层的根 CONNECT BY 指定阶层的父/子关系 NOCYCLE 存在CONNECT BY LOOP的纪录时，也返回查询结果。 condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr 例： CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ... CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id ... ■CONNECT_BY_ROOT 查询指定根的阶层数据。 ■CONNECT BY子句的例子 通过CONNECT BY子句定义职员和上司的关系。 SQL>SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID ----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101 ■LEVEL的例子 通过LEVEL虚拟列表示节点的关系。 SQL>SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL ----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 110 Chen 108 3 111 Sciarra 108 3 112 Urman 108 3 113 Popp 108 3 ■START WITH子句的例子 通过START WITH指定根节点，ORDER SIBLINGS BY保持阶层的顺序。 SQL>SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name; LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL ------------------------- ----------- ---------- ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar 173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 <place w:st="on"><city w:st="on"><span lang="EN-US" style="FONT-SIZE: 12pt; COLOR: black; FONT-FAMILY: 宋体; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">Austin</span></city></place> 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3 hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。 更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。 SQL>UPDATE employees SET manager_id = 145 WHERE employee_id = 100; SQL>SELECT last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; 2 3 4 5 6 7 ERROR: ORA-01436: CONNECT BY loop in user data CONNECT BY NOCYCLE强制返回查询结果。CONNECT_BY_ISCYCLE显示是否存在LOOP。 SQL>SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4; Employee Cycle LEVEL Path ------------------------- ------ ------ ------------------------- Russell 1 2 /King/Russell Tucker 0 3 /King/Russell/Tucker Bernstein 0 3 /King/Russell/Bernstein Hall 0 3 /King/Russell/Hall Olsen 0 3 /King/Russell/Olsen Cambrault 0 3 /King/Russell/Cambrault Tuvault 0 3 /King/Russell/Tuvault Partners 0 2 /King/Partners King 0 3 /King/Partners/King Sully 0 3 /King/Partners/Sully McEwen 0 3 /King/Partners/McEwen ■CONNECT_BY_ROOT的例子 1，查询110部门的职员，上司，职员和上司之间级别差及路径。 SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL > 1 and department_id = 110 CONNECT BY PRIOR employee_id = manager_id; Employee Manager Pathlen Path --------------- ------------ ---------- ----------------------------------- Higgins Kochhar 1 /Kochhar/Higgins Gietz Kochhar 2 /Kochhar/Higgins/Gietz Gietz Higgins 1 /Higgins/Gietz Higgins King 2 /King/Kochhar/Higgins Gietz King 3 /King/Kochhar/Higgins/Gietz 2，使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。 SELECT name, SUM(salary) "Total_Salary" FROM ( SELECT CONNECT_BY_ROOT last_name as name, Salary FROM employees WHERE department_id = 110 CONNECT BY PRIOR employee_id = manager_id) GROUP BY name; NAME Total_Salary ------------------------- ------------ Gietz 8300 Higgins 20300 King 20300 Kochhar 20300 1

参考二

对于oracle进行简单树查询(递归查询) DEPTID PAREDEPTID NAME NUMBER NUMBER CHAR (40 Byte) 部门id 父部门id(所属部门id) 部门名称 通过子节点向根节点追朔. select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid 通过根节点遍历子节点. select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid 可通过level 关键字查询所在层次. select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid 再次复习一下:start with ...connect by 的用法， start with 后面所跟的就是就是递归的种子。 递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询； connect by prior 后面所放的字段是有关系的，它指明了查询的方向。 对于prior，我是这样理解的： 上边例子中，比如：“prior deptid = paredeptid”，意思是：祖先（上一层记录）的deptid等于本条记录的paredeptid，即：通过根节点遍历子节点 练习： 通过子节点获得顶节点 select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid