时间戳

首页 > 解决方案 > ORDER BY 导致的 Oracle SQL 子查询语法错误

sql - ORDER BY 导致的 Oracle SQL 子查询语法错误

问题描述

我对 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
);

您知道如何解决第一个查询吗？还是简化第二个？先感谢您。

标签： sqloraclesubqueryora-00907

解决方案

在@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

干杯！！

推荐阅读