SQL加入两次相同的表？

I have this table:

我有这张桌子:

 CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY ,
    ENAME varchar2(10),
    JOB varchar2(9),
    MGR NUMBER(4)CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
    HIREDATE DATE,
    CONSTRAINT EMP_FOREIGN_KEY REFERENCES DEPT (DEPTNO));

And now i want to get the name (ename), job (job), and the name of the manager (mgr) of all workers.

现在我想得到所有工人的名字(ename),工作(工作)和经理姓名(mgr)。

I thought about doing this with a join on the same table:

我考虑过在同一个表上进行连接:

SELECT ename, job, empno
FROM emp
INNER JOIN emp AS emp1
ON (emp.mgr = emp1.empno);

But i allways get the error: "missing keyword"

但我总是得到错误:“缺少关键字”

3 个解决方案

#1

I think you have to remove AS from the inner join statement:

我认为你必须从内部连接语句中删除AS:

SELECT emp.ename, emp.job, emp.empno
FROM emp
INNER JOIN emp  emp1
ON (emp.mgr = emp1.empno);

And you have to add the alias before each column name.

您必须在每个列名称之前添加别名。

#2

SELECT E2.ename, E2.job, E2.empno, E1.ename as 'manager_name'
FROM emp as E1
INNER JOIN emp AS E2
ON E1.mgr = E2.empno;

#3

Try this:

SELECT e.ename, e.job, e.empno
FROM emp e
INNER JOIN emp e1
   ON (e.mgr = e1.empno);

#1