17,140
社区成员




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 a.员工号, a.上级
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 7
11 rows selected
select a.员工号, a.上级SQL> select *
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>
SQL> select *
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>