如果层级是固定的,那你可以用这张表进行自连,查出他的直接领导,再上层的领导也可以用类似的方法查出来。 select a.userid ,a.username,a.leaderid,b.username from sys_user a inner join sys_user b on a.leaderid=b.userid 。 如果层次是不固定的,那你可以用ORALE的层次相关的查询来完成,其实你这张表有一个父子关系。 select sys_connect_by_path(a.userid,'-') from sys_user a start with 初始条件 connect by a.userid=a.leaderid SQL不全,大意是这样,你自己再完善一下
select a.username as "本人",b.username as "上级",c.username as "上上级" from sys_user a , sys_user b, sys_user c where a.leaderid =b.userid and b.leaderid=c.userid;
select a.userid ,a.username,a.leaderid,b.username
from sys_user a
inner join sys_user b
on a.leaderid=b.userid 。
如果层次是不固定的,那你可以用ORALE的层次相关的查询来完成,其实你这张表有一个父子关系。
select sys_connect_by_path(a.userid,'-')
from sys_user a
start with 初始条件
connect by a.userid=a.leaderid
SQL不全,大意是这样,你自己再完善一下
(
userid number,
username varchar2(40),
leaderid NUMBER
)
;
INSERT INTO sys_user VALUES (1,'top manager',null);
INSERT INTO sys_user VALUES (20,'manager1',1);
INSERT INTO sys_user VALUES (30,'manager2',1);
INSERT INTO sys_user VALUES (201,'jeff',20);
INSERT INTO sys_user VALUES (202,'linda',20);
INSERT INTO sys_user VALUES (301,'jessie',30);
INSERT INTO sys_user VALUES (302,'kate',30);
INSERT INTO sys_user VALUES (303,'steven',30);
SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || username ,
CONNECT_BY_ROOT username 最大领导,
LEVEL ,
SYS_CONNECT_BY_PATH(username, '/') "PATH"
FROM sys_user
START WITH leaderid IS NULL
CONNECT BY PRIOR userid = leaderid ;
select a.username as "本人",b.username as "上级",c.username as "上上级"
from sys_user a , sys_user b, sys_user c
where a.leaderid =b.userid and b.leaderid=c.userid;