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;
| |
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 等进行层次查询 | |
| |
错误原因:在数据库表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 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
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
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
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进行简单树查询(递归查询)
|