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          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”或者“