表A
id num b_id
1 3 1
2 4 2
3 1 3
4 5 4
5 6 5
6 3 6
7 3 7
8 2 8
9 6 9
10 2 10
表B
id name managerId
1 nick
2 mily 1
3 nono 2
4 coco 2
5 tiki 1
6 mimi 5
7 lili 6
8 dada 7
9 wowo
10 hehe 9
A.b_id关联B.id
查找A表内容和A表中b_id 在B表中的他的二级经理名称查询结果为:
A.id A.num A.b_id B.name(二级经理)
1 3 1
2 4 2 mily
3 1 3 mily
4 5 4 mily
5 6 5 tiki
6 3 6 tiki
7 3 7 tiki
8 2 8 tik
9 6 9
10 2 10 hehe
id num b_id
1 3 1
2 4 2
3 1 3
4 5 4
5 6 5
6 3 6
7 3 7
8 2 8
9 6 9
10 2 10
表B
id name managerId
1 nick
2 mily 1
3 nono 2
4 coco 2
5 tiki 1
6 mimi 5
7 lili 6
8 dada 7
9 wowo
10 hehe 9
A.b_id关联B.id
查找A表内容和A表中b_id 在B表中的他的二级经理名称查询结果为:
A.id A.num A.b_id B.name(二级经理)
1 3 1
2 4 2 mily
3 1 3 mily
4 5 4 mily
5 6 5 tiki
6 3 6 tiki
7 3 7 tiki
8 2 8 tik
9 6 9
10 2 10 hehe
select a.*,b.name
from a,b
where a.b_id=b.id
drop table a;
create table a (id number, num number, b_id number);insert into a values (1, 3, 1);
insert into a values (2, 4, 2);
insert into a values (3, 1, 3);
insert into a values (4, 5, 4);
insert into a values (5, 6, 5);
insert into a values (6, 3, 6);
insert into a values (7, 3, 7);
insert into a values (8, 2, 8);
insert into a values (9, 6, 9);
insert into a values (10, 2, 10);
commit;drop table b;
create table b (id number, name varchar2(20), managerid number);insert into b values (1, 'nick', null);
insert into b values (2, 'mily', 1);
insert into b values (3, 'nono', 2);
insert into b values (4, 'coco', 2);
insert into b values (5, 'tiki', 1);
insert into b values (6, 'mimi', 5);
insert into b values (7, 'lili', 6);
insert into b values (8, 'dada', 7);
insert into b values (9, 'wowo', null);
insert into b values (10, 'hehe', 9);
commit; col lv2 format a20
select id, num, b_id, substr(path, instr(path, '/', 1, 2) + 1, (instr(path, '/', 1, 3) - instr(path, '/', 1, 2) - 1)) lv2 from (
select a.id, a.num, a.b_id, sys_connect_by_path(b.name, '/') || '/' path
from a, b
where a.b_id = b.id
start with b.managerId is null
connect by prior b.id = b.managerid
); ID NUM B_ID LV2
---------- ---------- ---------- --------------------
1 3 1
2 4 2 mily
3 1 3 mily
4 5 4 mily
5 6 5 tiki
6 3 6 tiki
7 3 7 tiki
8 2 8 tiki
9 6 9
10 2 10 hehe