//这会被执行
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
解决方法
您应该将其移出以修复语法：
SELECT empid FROM employees WHERE deptid IN
(
SELECT deptid FROM department WHERE description LIKE '%application%'
)
ORDER BY createddate DESC
createddateis not a column inemployeestable. It exists only indepartmenttable
然后,您需要加入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
