create table T
(
员工号 VARCHAR2(10),
上级 VARCHAR2(10)
)
;prompt Loading T...
insert into T (员工号, 上级)
values ('1', '2');
insert into T (员工号, 上级)
values ('2', '3');
insert into T (员工号, 上级)
values ('3', null);
insert into T (员工号, 上级)
values ('4', '3');
insert into T (员工号, 上级)
values ('5', '2');
insert into T (员工号, 上级)
values ('6', '7');
insert into T (员工号, 上级)
values ('7', '8');
insert into T (员工号, 上级)
values ('8', null);
insert into T (员工号, 上级)
values ('9', '11');
insert into T (员工号, 上级)
values ('10', '9');
insert into T (员工号, 上级)
values ('11', null);
commit;
上面是一个员工表,表中有一个上级字段,现在我想要一条select 员工号 from t,我要的效果是员工的上级员工必须排在他后面。
如员工1的上级员员为3
那么select 出来的时候员工3必须在员工1的后面
2 from t a
3 start with not exists (select 1 from t b where a.员工号 = b.上级)
4 connect by prior a.上级 = a.员工号;
员工号 上级
---------- ----------
1 2
2 3
3
10 9
9 11
11
4 3
3
5 2
2 3
3
6 7
7 8
8
14 rows selected
SQL>
2 from t a
3 start with a.上级 is null
4 connect by prior a.员工号 = a.上级
5 order by level desc;
员工号 上级
---------- ----------
1 2
5 2
6 7
10 9
2 3
4 3
9 11
7 8
3
11
8
11 rows selected
SQL>
from t a, t b
where a.上级 = b.员工号(+)
order by nvl(b.上级, nvl(a.上级, a.员工号)),
to_number(nvl(a.上级, '99')) desc
2 from t a, t b
3 where a.上级 = b.员工号(+)
4 order by nvl(b.上级, nvl(a.上级, a.员工号)),
5 to_number(nvl(a.上级, '99')) desc
6 ;员工号 上级
---------- ----------
11
9 11
10 9
3
4 3
2 3
5 2
1 2
8
7 8
6 711 rows selected
2 from t a, t b
3 where a.上级 = b.员工号(+)
4 order by nvl(b.上级, nvl(a.上级, a.员工号)),
5 to_number(nvl(a.上级, '99')) desc
6 ;员工号 上级
---------- ----------
11
9 11
10 9
3
4 3
2 3
5 2
1 2
8
7 8
6 711 rows selected