当我在 CTE 中尝试 with-clause 的概念时，Oracle 中缺少关键字错误

Question

下面是我的代码：

WITH Recursive EmployeeCTE AS
(
Select Employee_Id as e_id, first_Name as e_name, Manager_ID as mgr_id
From Employees
Where Employee_Id =&emp_id
union all
Select Employees.Employee_Id as e_id, Employees.first_Name as e_name, Employees.Manager_ID as mgr_id
From Employees
JOIN EmployeeCTE
ON Employees.Employee_Id = EmployeeCTE.Manager_ID
)
Select E1.first_name, NVL(E2.first_Name, 'No Boss') as Manager_Name
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.Manager_ID = E2.Employee_Id;

Answer 1

我认为从您遇到的错误中，请尝试提供别名，如下所示：

WITH EMPLOYEECTE(E_ID, E_NAME, MGR_ID) AS (
SELECT
EMPLOYEE_ID   AS E_ID,
FIRST_NAME    AS E_NAME,
MANAGER_ID    AS MGR_ID
FROM
EMPLOYEES
WHERE
EMPLOYEE_ID = &EMP_ID
UNION ALL
SELECT
EMPLOYEES.EMPLOYEE_ID   AS E_ID,
EMPLOYEES.FIRST_NAME    AS E_NAME,
EMPLOYEES.MANAGER_ID    AS MGR_ID
FROM
EMPLOYEES
JOIN EMPLOYEECTE ON EMPLOYEES.EMPLOYEE_ID = EMPLOYEECTE.MGR_ID
)
Select E1.FIRST_NAME,
NVL(E2.E_NAME, 'No Boss') AS MANAGER_NAME
FROM
EMPLOYEECTE E1
LEFT JOIN EMPLOYEECTE E2 ON E1.MANAGER_ID = E2.E_ID;

干杯！！