duplicate column name

Question

select e.empno,e.deptno,e.ename,d.ename from emp e,emp d where e.mgr=d.empno and e.hiredate<d.hiredate;     EMPNO     DEPTNO ENAME      ENAME
---------- ---------- ---------- ----------
      7499         30 ALLEN      BLAKE
      7521         30 WARD       BLAKE
      7698         30 BLAKE      KING
      7566         20 JONES      KING
      7782         10 CLARK      KING
      7369         20 SMITH      FORD6 rows selected.SQL> create table empa as select e.empno,e.deptno,e.ename,d.ename from emp e,emp d where e.mgr=d.empno and e.hiredate<d.hiredate;
create table empa as select e.empno,e.deptno,e.ename,d.ename from emp e,emp d where e.mgr=d.empno and e.hiredate<d.hiredate
                                                       *
ERROR at line 1:
ORA-00957: duplicate column name

Answer 1

这样就可以了 SQL> create table empa as select e.empno,e.deptno,e.ename "employee",d.ename "mgr" from emp e,emp d where e.mgr=d.empno and e.hiredate<d.hiredate;Table created.

Answer 2

但是后边很奇怪SQL> select a.employee,a.empno,b.dname,b.deptno from empa a,dept b where a.deptno=b.deptno;
select a.employee,a.empno,b.dname,b.deptno from empa a,dept b where a.deptno=b.deptno
       *
ERROR at line 1:
ORA-00904: "A"."EMPLOYEE": invalid identifier如果没有 a.employee 就正常了
SQL> select a.empno,b.dname,b.deptno from empa a,dept b where a.deptno=b.deptno;     EMPNO DNAME              DEPTNO
---------- -------------- ----------
      7782 ACCOUNTING             10
      7566 RESEARCH               20
      7369 RESEARCH               20
      7521 SALES                  30
      7499 SALES                  30
      7698 SALES                  306 rows selected.
SQL> desc empa;
 Name                                                                                                                                                       e
 ------------------------------------------------------------------------------------------------------------------------------------------------------------
 EMPNO                                                                                                                                                      )
 DEPTNO                                                                                                                                                     )
 employee                                                                                                                                                   )
 mgr  

Answer 3

你之前是这样e.ename,d.ename ，两个ename自然错
你现在并没重复列，自然没报错..

Answer 4

我是把e.ename 转换成了 empa 表中的 employee
      d.ename 转换成了 empa 表中的 mgr但是后边 empa a， dept b，之后
 用a.employee  也不行这个就不理解了， 毕竟a.employee 只是对应e.ename 后来发现 这样是可以的， 就是说不要 把e.ename 和d.ename 都写入到empa 表， 好像就可以了（）
SQL> SELECT a.empno,a.ename,b.dname
  2  FROM
  3  (SELECT e.empno,e.ename,e.deptno
  4  FROM emp e, emp d WHERE e.mgr=d.empno AND e.hiredate<d.hiredate) a,dept b
  5  WHERE a.deptno=b.deptno;     EMPNO ENAME      DNAME
---------- ---------- --------------
      7499 ALLEN      SALES
      7521 WARD       SALES
      7698 BLAKE      SALES
      7566 JONES      RESEARCH
      7782 CLARK      ACCOUNTING
      7369 SMITH      RESEARCH6 rows selected.
 

Answer 5

employee
  有这个字段吗？

Answer 6

SQL> create table empa as select e.empno,e.deptno,e.ename "employee",d.ename "mgr" from emp e,emp d where e.mgr=d.empno and e.hiredate<d.hiredate;这样建立的