ORDER by导致的Oracle SQL子查询语法错误

Question

我在Oracle SQL查询中出现语法错误。这个查询应该能让我找到一名员工的部门。如果员工没有部门(null(，我想要层次结构中第一个经理的部门，它有一个部门。

SELECT department_id FROM department
WHERE department_id =
(
SELECT department_id FROM employee
WHERE department_id IS NOT NULL AND rownum = 1
start WITH employee_id = 19
connect by employee_id = prior manager_id
ORDER BY level
);

错误消息如下：

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 8 Column: 2

我写了一个替代查询，它完成了这项工作。但我对此不太满意。

SELECT department_id FROM department
WHERE department_id = 
(
SELECT department_id FROM employee
WHERE level =
(
SELECT MIN(level) FROM employee
WHERE department_id IS NOT NULL
start WITH employee_id = 19
connect by employee_id = prior manager_id
)
start WITH employee_id = 19
connect by employee_id = prior manager_id
);

你知道如何修复第一个查询吗？还是简化第二个？提前谢谢。

Answer 1

在你的第一个查询中，@Barbaros正确地说ORDER BY不需要，甚至rownum = 1也不会做你想做的事。

以下查询是否满足您的要求：

SELECT
E.EMPLOYEE_ID,
CASE WHEN E.DEPARTMENT_ID IS NOT NULL 
THEN E.DEPARTMENT_ID
ELSE LAG(D.DEPARTMENT_ID IGNORE NULLS) OVER(ORDER BY LEVEL)
END AS DEPARTMENT_ID
FROM
EMPLOYEE E
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.EMPLOYEE_ID = 19
CONNECT BY E.EMPLOYEE_ID = PRIOR E.MANAGER_ID

干杯！！