sql - Oracle select 查询出现内部 select 查询错误

Question

我收到 SQL 错误并尝试解决，任何指针都会有帮助，

//这被执行

从员工中选择 empid WHERE deptid IN (10,20,30,40 );

//这被执行

SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC 

但是下面的查询会抛出错误:

SELECT empid  FROM employees WHERE deptid IN (SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC);

错误:ORA-00907: 缺少右括号00907. 00000 - “缺少右括号”

更新:07/14:

使用 @dasblinkenlight 的确切解决方案进行更新:

The problem is placing ORDER BY in the WHERE clause subquery. SQL syntax does not allow you to order elements of the subquery in the WHERE clause, because it does not change the result of the query overall

本文很好地解释了许多概念 - http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html

"ORA-00907: missing right parenthesis Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:"

我尝试使用 WHERE 子句和“=”而不是“IN”的 SQL 语句，但它仍然抛出错误:“缺少右括号”。

结论1:

“不要在 WHERE 子句子查询中使用 ORDER BY” 或者“Oracle中where子句中的子查询不允许使用ORDER BY”

结论2

这个案例研究还展示了我们应该使用 JOIN 而不是选择子查询的场景

Answer 1

问题在于将 ORDER BY 放在 WHERE 子查询中。 SQL 语法不允许您在 WHERE 子句中对子查询的元素进行排序，因为它不会更改整个查询的结果。

您应该将其移出以修复语法:

SELECT empid  FROM employees WHERE deptid IN
    (
        SELECT deptid FROM department WHERE description LIKE '%application%'
    )
ORDER BY createddate DESC

createddate is not a column in employees table. It exists only in department table

然后，您需要加入 department 表，并在其列之一上使用 ORDER BY:

SELECT e.empid
FROM employees e
JOIN department d ON e.deptid = d.deptid
WHERE d.description LIKE '%application%'
ORDER BY d.createddate DESC