Oracle是否允许在IN子句中使用ORDER BY？

Oracle is giving me an error (ORA-00907: missing right parenthesis) when I run this query:

当我运行此查询时,Oracle给出了一个错误(ORA-00907:缺少右括号):

select * 
from reason_for_appointment 
where reason_for_appointment_id in 
(
    select reason_for_appointment_id 
    from appointment_reason 
    where appointment_id = 11 
    order by appointment_reason_id
)

However, when I run just the subquery, there's no error.

但是,当我只运行子查询时,没有错误。

Can anyone explain what the problem is?

任何人都可以解释问题是什么?

5 个解决方案

#1

The problem is that ORDER BY is not permiited inside a subquery like this one. Why did you want to have one?

问题是ORDER BY没有像这样的子查询中的内容。你为什么想要一个?

#2

The inner query results will never be displayed, so theres no point in doing the order by in the nested select. Apply it to the outer query instead.

内部查询结果将永远不会显示,因此在嵌套选择中执行顺序没有意义。而是将其应用于外部查询。

#3

It looks like you're wanting to display the results from one table using an ordering defined in another table. An inner join should suffice.

看起来您希望使用另一个表中定义的排序显示一个表的结果。内连接应该足够了。

select reason_for_appointment.*
from reason_for_appointment rfa, appointment_reason ar
where rfa.reason_for_appointment_id = ar.reason_for_appointment_id
and ar.appointment_id = 11
order by ar.appointment_reason_id;

#4

select * from reason_for_appointment where reason_for_appointment_id in (select reason_for_appointment_id from appointment_reason where appointment_id = 11 order by appointment_reason_id)

select * from reason_for_appointment其中reason_for_appointment_id in(从appointment_reason中选择reason_for_appointment_id,其中appointment_id = 11 by appointment_reason_id)

try something like: with auxiliar as (select reason_for_appointment_id from appointment_reason where appointment_id = 11 order by appointment_reason_id) select reason_for_appointment_id from appointment_reason where reason_for_appointment_id in (select reason_for_appointment_id from auxiliar)

尝试类似:使用辅助as(从appointment_reason中选择reason_for_appointment_id,其中appointment_id =由ordered_reason_id命令11)从appointment_reason中选择reason_for_appointment_id,其中reason_for_appointment_id in(从辅助中选择reason_for_appointment_id)

#5

If your goal is to have the output ordered, you simply want to move the ORDER BY outside of the subquery:

如果您的目标是订购输出,您只需要在子查询之外移动ORDER BY:

select * from reason_for_appointment where reason_for_appointment_id in
 (select reason_for_appointment_id from appointment_reason where appointment_id = 11)
 order by reason_for_appointment_id

( I'm assuming that where you wrote "appointment_reason_id" you meant "reason_for_appointment_id". If there really are two different columns with these names ... ouch.)

(我假设你写“appointment_reason_id”的地方你的意思是“reason_for_appointment_id”。如果确实有两个不同的列有这些名字......哎哟。)

#1