ORACLE查询之用自连同时显示雇员和老板的名字

以scott emp表为例
select a.ename as employee_name,b.ename as boss_name from emp a,emp b where a.mgr = b.empno;

思路
1 确定自联，查询后发现是一个笛卡尔积
分析后发现，雇员和老板的对应关系是 7369 SMITH 7902 FORD

2 根据上述分析，将约束条件加上
select a.empno,a.ename,a.mgr,b.empno,b.ename,b.mgr from emp a,emp b where a.mgr = b.empno;

3 筛选投影后取别名
select a.ename as employee_name,b.ename as boss_name from emp a,emp b where a.mgr = b.empno;

EMPLOYEE_N BOSS_NAME
---------- ----------
FORD       JONES
SCOTT      JONES
TURNER     BLAKE
ALLEN      BLAKE
WARD       BLAKE
JAMES      BLAKE
MARTIN     BLAKE
MILLER     CLARK
ADAMS      SCOTT
BLAKE      KING
JONES      KING
CLARK      KING
SMITH      FORD

13 rows selected.