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

select AD_DEPARTMENT_CODE, AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_NAME   from (select AD_DEPARTMENT_CODE,                AD_DEPARTMENT_UP_LEVEL,                AD_DEPARTMENT_NAME           from AD_DEPT_ARCHITECTURE          orderby AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_CODE)  start withAD_DEPARTMENT_CODE = '0' connect by priorAD_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           FROMCOLUMNS 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(+))  STARTWITH APR_CODE = '0|0|0' CONNECTBYPRIOR APR_CODE = APR_CODE_UP   这里利用'-'和'|'来进行区分，找出了树状结构的根 为了构建树， （1）先建立了树结构的根，字段这里都设为“0”或者“00” （2）然后再根据需要填充数据 （3）利用上边的oracle的PRIOR，CONNECT_BY_ROOT 等进行层次查询

递归查询时：由于数据类型不正确而导致 “ ORA-01436: CONNECT BY loop in user data” 错误原因：在数据库表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          ORDERBY TAPE_NO, TRANSFER)  STARTWITH TRANSFER = 0 CONNECTBYPRIORTO_CHAR(TRANSFER) = TAPE_NO

 

 

参考一

PL/SQL基础：阶层查询 作者：itpub cxck  2007-05-08 内容导航：  ORACLE 10g新增了阶层查询操作符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 1001 Cambrault 1481002 Bates 1721483 Bloom 1691483 Fox 1701483 Kumar 1731483 Ozer 1681483 Smith 1711483 De Haan 1021002 Hunold 1031023 Austin1051034 Ernst 1041034 Lorentz 1071034 Pataballa 1061034 Errazuriz 1471002 Ande 1661473 Banda 1671473   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 WHERElevel<=3AND department_id =80 START WITH last_name ='King' CONNECT BY PRIOR employee_id = manager_id ANDLEVEL<=4; 234567 ERROR: ORA-01436: CONNECT BY loop inuser 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 WHERElevel<=3AND department_id =80 START WITH last_name ='King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id ANDLEVEL<=4; Employee Cycle LEVEL Path ------------------------- ------ ------ ------------------------- Russell 12/King/Russell Tucker 03/King/Russell/Tucker Bernstein 03/King/Russell/Bernstein Hall 03/King/Russell/Hall Olsen 03/King/Russell/Olsen Cambrault 03/King/Russell/Cambrault Tuvault 03/King/Russell/Tuvault Partners 02/King/Partners King 03/King/Partners/King Sully 03/King/Partners/Sully McEwen 03/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 WHERELEVEL>1and 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) GROUPBY 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