排序时如何不用 siblings 关键字提供的特性进行相同的排序输出 ?

显示全部楼层 · 2008-11-14 14:42:19

排序时如何不用 siblings 关键字提供的特性进行相同的排序输出 ?
曾经有个需求就是在低版本Oracle中要实现这个功能. 当时有个客观的条件就是层次 level 最多为 3 层
我写了个比较复杂的, 希望能看到更简单的写法.
select cast(lpad(empno,level*4)||ename as varchar2(25)) empnoname,hiredate,mgr
from emp
start with mgr is null
connect by prior empno = mgr
order siblings by hiredate
/
EMPNONAME
HIREDATEMGR
------------------------- ----------- -----
7839KING
1981-11-17
7566JONES
1981-04-02 7839
7902FORD
1981-12-03 7566

7369SMITH 1980-12-17 7902
7788SCOTT 1987-04-19 7566

7876ADAMS 1987-05-23 7788
7698BLAKE
1981-05-01 7839
7499ALLEN 1981-02-20 7698
7521WARD
1981-02-22 7698
7844TURNER1981-09-08 7698
7654MARTIN1981-09-28 7698
7900JAMES 1981-12-03 7698
7782CLARK
1981-06-09 7839
7934MILLER1982-01-23 7782

14 rows selected

千问 · 2008-11-14 14:42:19

select cast(lpad(empno, length(lev) / 2) || ename as varchar2(25)) empnoname,
hiredate,
mgr
from (select lev || to_char(emp.hiredate, 'yyyymmdd') lev,

emp.empno,

emp.ename,

emp.hiredate,

emp.mgr

from emp,

(select lev || to_char(emp.hiredate, 'yyyymmdd') lev, emp.empno

from emp,

(select lev || to_char(emp.hiredate, 'yyyymmdd') lev, emp.empno

from emp,

(select to_char(emp.hiredate, 'yyyymmdd') lev, empno

from emp

where mgr is null) emp1

where emp.mgr = emp1.empno) emp2

where emp.mgr = emp2.empno) emp3
where emp.mgr = emp3.empno
union all
select lev || to_char(emp.hiredate, 'yyyymmdd') lev,

emp.empno,

emp.ename,

emp.hiredate,

emp.mgr

from emp,

(select lev || to_char(emp.hiredate, 'yyyymmdd') lev, emp.empno

from emp,

(select to_char(emp.hiredate, 'yyyymmdd') lev, empno

from emp

where mgr is null) emp1

where emp.mgr = emp1.empno) emp2
where emp.mgr = emp2.empno
union all
select lev || to_char(emp.hiredate, 'yyyymmdd') lev,

emp.empno,

emp.ename,

emp.hiredate,

emp.mgr

from emp,

(select to_char(emp.hiredate, 'yyyymmdd') lev, empno

from emp

where mgr is null) emp1
where emp.mgr = emp1.empno
union all
select to_char(emp.hiredate, 'yyyymmdd') lev, empno, ename, hiredate, mgr

from emp
where mgr is null)
order by lev;
顶一下

感觉我的方法比较笨.

千问 · 2008-11-14 14:42:19

晕, 刚刚想到一个利用 sys_connect_by_path 的结果来排序的方法.
select cast(lpad(empno,level*4)||ename as varchar2(25)) empnoname,hiredate,mgr
,sys_connect_by_path(to_char(hiredate,'yyyymmdd'),'.') path
from emp
start with mgr is null
connect by prior empno = mgr
order by path;

千问 · 2008-11-14 14:42:19

唉, 温故而知新啊.... 灌个水..

千问 · 2008-11-14 14:42:19

你这个order by path好像和原来的顺序不等价啊。如果同级别有hiredate相同的就会乱了。比如经理2就会插入到经理1和他的下属之中。
修改为：
sys_connect_by_path(to_char(hiredate,'yyyymmdd')||LPAD(EMPNO,10),'.') AS PATH

千问 · 2008-11-14 14:42:19

原帖由 newkid 于 2008-10-28 23:03 发表
你这个order by path好像和原来的顺序不等价啊。如果同级别有hiredate相同的就会乱了。比如经理2就会插入到经理1和他的下属之中。
修改为：
sys_connect_by_path(to_char(hiredate,'yyyymmdd')||LPAD(EMPNO,10),'.') AS PATH
嗯, 真是的耶, 谢谢