在Oracle11g中有个EMP表,然后想用一个嵌套CTE SQL查询出每个员工与领导之间的关系。这个SQL可以在SQL Server但是不能再Oracle中运行。代码和报错如下:
SQL> WITH SCOTT_EMP(EMPNO,MGR,ENAME,T) AS(
2 SELECT EMPNO,MGR,ENAME,0 T FROM EMP WHERE MGR IS NULL
3 UNION all
4 SELECT E.EMPNO,E.MGR,E.ENAME,M.T+1 FROM EMP E
5 INNER JOIN SCOTT_EMP M ON E.MGR=M.EMPNO
6 )
7 SELECT * FROM SCOTT_EMP;
WITH SCOTT_EMP(EMPNO,MGR,ENAME,T) AS(
*
ERROR at line 1:
ORA-32033: unsupported column aliasing
SQL>
SQL> WITH SCOTT_EMP(EMPNO,MGR,ENAME,T) AS(
2 SELECT EMPNO,MGR,ENAME,0 T FROM EMP WHERE MGR IS NULL
3 UNION all
4 SELECT E.EMPNO,E.MGR,E.ENAME,M.T+1 FROM EMP E
5 INNER JOIN SCOTT_EMP M ON E.MGR=M.EMPNO
6 )
7 SELECT * FROM SCOTT_EMP;
WITH SCOTT_EMP(EMPNO,MGR,ENAME,T) AS(
*
ERROR at line 1:
ORA-32033: unsupported column aliasing
SQL>
根据Oracle官方文档描述,貌似Oracle11g R2之后才支持增强型的WITH 语句