sql – 具有内部选择查询错误的Oracle选择查询

//这会被执行

SELECT empid FROM employees 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”
或者“where子句中的子查询不允许在Oracle中使用ORDER BY”

结论2

本案例研究还显示了我们应该选择JOIN而不是选择子查询的场景

解决方法

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!