带有内部选择查询错误的Oracle选择查询 [英] Oracle select query with inner select query error

问题描述

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

I am getting a SQL error and trying to resolve, any pointers would be helpful,

//这将被执行

SELECT empid FROM employees WHERE deptid IN (10,20,30,40 );

//这将被执行

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

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

but the below query throws error:

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

错误: ORA-00907:右括号缺失 00907. 00000-缺少右括号"

error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

更新:07/14:

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

Updating with the exact solution from @dasblinkenlight:

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

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

This article well explains many of the concepts - http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html

"ORA-00907:缺少右括号当收到消息时很明显 像这样，第一个反应可能是验证括号 已被排除在外，但不幸的是没有缺少括号 在此声明中根本没有.

"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.

简而言之，不为人知的语法怪癖总结如下: 不要在IN子查询中使用ORDER BY.

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

现在，有人可能会反对说使用ORDER确实没有任何意义 IN子句中的BY，这是正确的，因为Oracle不在乎 关于IN子句中的行顺序:"

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语句，但仍然抛出错误:'missing right parenthesis'.

I tried the SQL statement with WHERE clause and '=' instead of 'IN' and it still threw the error:'missing right parenthesis'.

结论1 :

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

"Don't use ORDER BY in the WHERE clause subquery" or "Subqueries in the where clause are not allowed to use ORDER BY in Oracle"

结论2

此案例研究还显示了我们应该进行JOIN而不是选择子查询的情况

This case-study also shows the scenario where we should go for JOIN rather than select subquery

推荐答案

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

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.

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

You should move it out to fix the syntax:

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

createddate不是employees表中的列.它仅存在于department表中

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

然后，您需要连接到department表，并在其其中一列上使用ORDER BY:

Then you need to join to the department table, and use ORDER BY on one of its columns:

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

这篇关于带有内部选择查询错误的Oracle选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！