select em.ename as empname, em.job, mgr.ename as mgrname, dep.dname from scott.emp em, scott.emp mgr, scott.dept dep where em.mgr = mgr.empno and em.deptno = dep.deptno
简单啊,这个叫自连接,即连接的2个表或多个表是同一个表。SELECT EMP1.Ename AS "员工姓名", EMP1.Job AS "员工职位", EMP2.Ename AS "领导姓名", DName FROM EMP EMP1 JOIN EMP EMP2 ON EMP1.MGR = EMP2.EMPNO JOIN DEPT ON EMP1.DEPTNO = DEPT.DEPTNO;
from scott.emp em, scott.emp mgr, scott.dept dep
where em.mgr = mgr.empno and em.deptno = dep.deptno
FROM EMP EMP1 JOIN EMP EMP2 ON EMP1.MGR = EMP2.EMPNO
JOIN DEPT ON EMP1.DEPTNO = DEPT.DEPTNO;