oracle 我可以在子查询中使用“ORDER BY”子句吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34378701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I use "ORDER BY" clause in a subquery?
提问by sql_dummy
SELECT * from table1 where column1 IN (SELECT column1 from table1 ORDER BY column1);
回答by Mureinik
You cannot use an order byclause in a query that's used with the inoperator. I'm guessing the reason to deny its use is because it would be pointless - an incondition should return true if the left-hand side operand is present in the result of the right hand side query, regardless of its relative position in the result. So allowing you to use an order byclause there would leave Oracle's developers with one of two unappealing choices:
您不能order by在与in运算符一起使用的查询中使用子句。我猜测拒绝使用它的原因是因为它毫无意义 -in如果左侧操作数出现在右侧查询的结果中，则条件应该返回 true，无论其在结果中的相对位置如何。因此，允许您在order by那里使用子句将使 Oracle 的开发人员面临以下两个不吸引人的选择之一：
- Perform a costly, useless, sorting, or
- Silently ignore the
order byclause.
- 执行昂贵的、无用的、排序或
- 默默无视这个
order by条款。
Neither of these options fit well to Oracle Database's mindset, so I'm guessing the easiest thing would be to just block this option.
这两个选项都不适合 Oracle 数据库的思维方式，所以我猜最简单的方法就是阻止这个选项。
回答by Rahul
Simple and straight answer NO. Why? Purpose of order byis to provide sorting functionality to the resultant data and subquery/inner query data is not the final output rather the partial data which is going to be manipulated further using the outer query and so having an order byin subquery makes no sense at all and illogical altogether. You should rather have the order in your main query like
简单而直接的回答否。为什么？目的order by是为结果数据提供排序功能，子查询/内部查询数据不是最终输出，而是将使用外部查询进一步操作的部分数据，因此使用order by子查询完全没有意义且不合逻辑共。您应该在主查询中使用订单，例如
SELECT * from table1
where column1 IN (SELECT column1 from table1)
ORDER BY column1;
回答by Marmite Bomber
If the question is can I use ORDER BY, the answer is very straightforward. The Oracle syntax check either accept the ORDER BY or reject it.
如果问题是我可以使用 ORDER BY，那么答案非常简单。Oracle 语法检查要么接受 ORDER BY，要么拒绝它。
In your example
在你的例子中
SELECT * from tab1 where col1 IN (SELECT col1 from tab1 ORDER BY col1);
You get an error
你得到一个错误
ORA-00907: missing right parenthesis
The syntax checker doesn't accept the ORDER BY in the subquery and complaints about missing of the closing parenthesis. That it is syntax check you may see on the fact, that you get the very same error even if the table doesn't exists. (It should be mentioned, that in some cases the allowed syntax is an extension to the standards).
语法检查器不接受子查询中的 ORDER BY 和关于缺少右括号的抱怨。这是您可能会看到的语法检查，即使该表不存在，您也会收到相同的错误。（应该提到的是，在某些情况下，允许的语法是标准的扩展）。
A question should I use ORDER BYis completely different and as pointed out discussed elsewhere.
一个问题，我应该使用ORDER BY是完全不同的，因为其他地方讨论指出。
ADDED EXAMPLES
增加的例子
Here an example where ORDER BY in subquery is allowed
这是一个允许在子查询中使用 ORDER BY 的示例
WITH t AS
(SELECT col1 FROM tab ORDER BY col1
)
SELECT * FROM t;
In contrary this leads to a syntax error
相反，这会导致语法错误
WITH t AS
( SELECT col1 FROM tab ORDER BY col1
UNION ALL
SELECT col1 FROM tab ORDER BY col1
)
SELECT * FROM t;
