--用自连接就可以了 那emp表举例--如果fatherID没有为null的 select t.*,t1.empno mgrno ,t1.ename mgrname from emp t,emp t1 where t.mgr=t1.empnoEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGRNO MGRNAME ----- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- -- 7369 SMITH CLERK 7902 1980-12-17 800 900 20 7902 FORD 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7698 BLAKE 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7698 BLAKE 7566 JONES MANAGER 7839 1981-04-02 2975 900 20 7839 KING 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7839 KING 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7839 KING 7788 SCOTT ANALYST 7566 1987-04-19 3000 900 20 7566 JONES 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7698 BLAKE 7876 ADAMS CLERK 7788 1987-05-23 1100 900 20 7788 SCOTT 7900 JAMES CLERK 7698 1981-12-03 950 30 7698 BLAKE 7902 FORD ANALYST 7566 1981-12-03 3000 900 20 7566 JONES 7934 MILLER CLERK 7782 1982-01-23 1430 10 7782 CLARK --fatherID有为null的 select t.*,t1.empno mgrno ,t1.ename mgrname from emp t,emp t1 where t.mgr=t1.empno(+)EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGRNO MGRNAME ----- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- -- 7369 SMITH CLERK 7902 1980-12-17 800 900 20 7902 FORD 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7698 BLAKE 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7698 BLAKE 7566 JONES MANAGER 7839 1981-04-02 2975 900 20 7839 KING 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7839 KING 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7839 KING 7788 SCOTT ANALYST 7566 1987-04-19 3000 900 20 7566 JONES 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7698 BLAKE 7876 ADAMS CLERK 7788 1987-05-23 1100 900 20 7788 SCOTT 7900 JAMES CLERK 7698 1981-12-03 950 30 7698 BLAKE 7902 FORD ANALYST 7566 1981-12-03 3000 900 20 7566 JONES 7934 MILLER CLERK 7782 1982-01-23 1430 10 7782 CLARK
嗯,使用自连接.select a.*, b.ID,b.Name,b.FatherID from tbname a, tbname b where a.id = b.FatherID;
通过本机构查询父机构sql如下(包括本机构):select * from eosorg_t_organization start with orgid=61 connect by prior parentorgid=orgid;通过本机构查询子机构sql如下(包括本机构):select * from eosorg_t_organization orgid start with orgid=21 connect by prior orgid = parentorgid;
--用自连接就可以了 那emp表举例--如果fatherID没有为null的
select t.*,t1.empno mgrno ,t1.ename mgrname
from emp t,emp t1
where t.mgr=t1.empnoEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGRNO MGRNAME
----- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- --
7369 SMITH CLERK 7902 1980-12-17 800 900 20 7902 FORD
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7698 BLAKE
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7698 BLAKE
7566 JONES MANAGER 7839 1981-04-02 2975 900 20 7839 KING
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE
7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7839 KING
7782 CLARK MANAGER 7839 1981-06-09 2450 10 7839 KING
7788 SCOTT ANALYST 7566 1987-04-19 3000 900 20 7566 JONES
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7698 BLAKE
7876 ADAMS CLERK 7788 1987-05-23 1100 900 20 7788 SCOTT
7900 JAMES CLERK 7698 1981-12-03 950 30 7698 BLAKE
7902 FORD ANALYST 7566 1981-12-03 3000 900 20 7566 JONES
7934 MILLER CLERK 7782 1982-01-23 1430 10 7782 CLARK
--fatherID有为null的
select t.*,t1.empno mgrno ,t1.ename mgrname
from emp t,emp t1
where t.mgr=t1.empno(+)EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGRNO MGRNAME
----- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- --
7369 SMITH CLERK 7902 1980-12-17 800 900 20 7902 FORD
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7698 BLAKE
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7698 BLAKE
7566 JONES MANAGER 7839 1981-04-02 2975 900 20 7839 KING
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE
7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7839 KING
7782 CLARK MANAGER 7839 1981-06-09 2450 10 7839 KING
7788 SCOTT ANALYST 7566 1987-04-19 3000 900 20 7566 JONES
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7698 BLAKE
7876 ADAMS CLERK 7788 1987-05-23 1100 900 20 7788 SCOTT
7900 JAMES CLERK 7698 1981-12-03 950 30 7698 BLAKE
7902 FORD ANALYST 7566 1981-12-03 3000 900 20 7566 JONES
7934 MILLER CLERK 7782 1982-01-23 1430 10 7782 CLARK
from tbname a, tbname b
where a.id = b.FatherID;