有没有一种方法可以在 Oracle CONNECT BY 查询中按 DESCENDING 排序？

Question

在这个场景中......

CREATE TABLE emp 
(
    empno  NUMBER(4) NOT NULL,
    ename  VARCHAR2(10),
    job    VARCHAR2(9),
    mgr    NUMBER(4),
    sal    NUMBER(7,2),
    deptno NUMBER(2)
);

INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, 2450, 10);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, 800, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698, 1600, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698, 1250, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839, 2975, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698, 1250, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, 2850, 30);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566, 3000, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, 5000, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698, 1500, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788, 1100, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698, 950, 30);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566, 3000, 60);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782, 1300, 10);

COMMIT;

SELECT LPAD(' ', 4  *(LEVEL - 1)) ||  empno || ' ' || mgr hier
   FROM emp
   start with mgr is null
   CONNECT BY PRIOR empno = mgr;

DROP TABLE emp;

结果看起来像这样。

7839
    7566 7839
        7788 7566
            7876 7788
        7902 7566
            7369 7902
    7698 7839
        7499 7698
        7521 7698
        7654 7698
        7844 7698
        7900 7698
    7782 7839    
        7934 7782

看来在连接过程中，经理最终按升序排列。 有什么方法可以控制这种行为，从而使它们按降序显示吗？

7839                  
    7782 7839         
        7934 7782   
    7698 7839         
        7900 7698   
        7844 7698     
        7654 7698     
        7521 7698     
        7499 7698     
    7566 7839         
        7788 7566     
            7876 7788 
        7902 7566     
            7369 7902 

感谢

邪恶

Answer 1

使用“ORDER SIBLINGS BY”子句对层次结构中的同级进行排序。

http://docs.oracle.com/database/121/SQLRF/queries003.htm

SELECT LPAD(' ', 4  *(LEVEL - 1)) ||  empno || ' ' || mgr hier
   FROM emp
   start with mgr is null
   CONNECT BY PRIOR empno = mgr
   ORDER SIBLINGS by empno desc;