Oracle：Order by Union 返回 ORA-00933：SQL 命令未正确结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23156727/
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
Oracle: Order by Union returning ORA-00933: SQL command not properly ended
提问by user2928913
I have an issue with using Oracle's union and order by clauses together.
我在一起使用 Oracle 的 union 和 order by 子句时遇到问题。
I have two complex queries (with sub queries in them) having an order by clause for each of them. I need to union the output of both and return the result. When I run it, I am getting the error ORA-00933: SQL command not properly ended.
我有两个复杂的查询（其中包含子查询），每个查询都有一个 order by 子句。我需要合并两者的输出并返回结果。当我运行它时，我收到错误 ORA-00933：SQL 命令未正确结束。
But it works when I comment out the order by clauses in both of them.
但是当我注释掉它们两个中的 order by 子句时它就起作用了。
To test this, I created a simple query as simple as shown below
为了测试这一点，我创建了一个简单的查询，如下所示
select * from employee where employee_id=2 order by name
union
select * from employee where employee_id=3 order by name;
Even this gave the same error when ran with order by clauses but runs well when I commentout the order by clauses.
即使在使用 order by 子句运行时也会出现相同的错误，但是当我注释掉 order by 子句时运行良好。
I tried searching forums, but I could not get solution for the exact problem. I found one at ORACLE Query with ORDER BY and UNIONbut As my queries are already too complecated because of subqueries and joins between too many tables, I dont want to implement this.
我尝试搜索论坛，但无法找到确切问题的解决方案。我在带有 ORDER BY 和 UNION 的 ORACLE Query 上找到了一个，但是由于子查询和连接太多表，我的查询已经太复杂了，我不想实现这个。
Can someone help me on fixing the root cause of the issue.
有人可以帮助我解决问题的根本原因。
采纳答案by Eng. Samer T
try this code:
试试这个代码：
select e1.name name /* e1.* */
from employee e1
where employee_id = 2
union
select
e2.name name /* e2.* */
from employee e2
where employee_id = 3
order by name;
if you want to order the result of first query then to order the result the second query so you can do like this:
如果您想对第一个查询的结果进行排序，然后对第二个查询的结果进行排序，那么您可以这样做：
select 1 query, e1.name name /* e1.* */
from employee e1
where employee_id = 2
union
select
2 query, e2.name name /* e2.* */
from employee e2
where employee_id = 3
order by query, name;
回答by Wolf
You can have only one ORDER BYwhen combining multiple queries, on the last statement. The ORDER BYclause acts on the entireset.
ORDER BY组合多个查询时，最后一条语句只能有一个。该ORDER BY子句作用于整个集合。
See the Oracle Documentation:
请参阅Oracle 文档：
You cannot specify the order_by_clause in the subquery of these operators.
您不能在这些运算符的子查询中指定 order_by_clause。
