ORA-00933 UNION 与 ORDER BY
The UNION operator returns only distinct rows that appear in either result,
while the UNION ALL operator returns all rows.
The UNION ALL operator does not eliminate duplicate selected rows
union返回不重复行。 union all 返回所有行。
You have to use the Order By at the end of ALL the unions。
the ORDER BY is considered to apply to the whole UNION result
(it's effectively got lower binding priority than the UNION).
The ORDER BY clause just needs to be the last statement, after you've done all your unioning.
You can union several sets together, then put an ORDER BY clause after the last set.
ORDER BY 是针对整个union的结果集order by,只能用在union的最后一个子查询中。
SQL> SELECT employee_id, department_id
2 FROM employees
3 WHERE department_id = 50
4 ORDER BY department_id
5 UNION
6 SELECT employee_id, department_id
7 FROM employees
8 WHERE department_id = 90
9 ;
SELECT employee_id, department_id
FROM employees
WHERE department_id = 50
ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id = 90
ORA-00933: SQL 命令未正确结束
SQL>
SQL> SELECT employee_id, department_id
2 FROM employees
3 WHERE department_id = 50
4 UNION
5 SELECT employee_id, department_id
6 FROM employees
7 WHERE department_id = 90
8 ORDER BY department_id;
EMPLOYEE_ID DEPARTMENT_ID
---------------- --------------------
120 50
121 90
也可以用在排序中使用列号,代替列名
SQL> SELECT employee_id, department_id
2 FROM employees
3 WHERE department_id = 50
4 UNION
5 SELECT employee_id, department_id
6 FROM employees
7 WHERE department_id = 90
8 ORDER BY 2
9 ;
EMPLOYEE_ID DEPARTMENT_ID
---------------- --------------------
120 50
121 90
推荐阅读
-
Hive-distribute by与group by,order by与sort by 的区别,cluster by-distribute by与group by 的区别
-
比较MySQL 5.7与8.0版的分组排序技巧:理解变量@rank与Row_number() over( order by ... )的区别
-
理解与应用:SQL UNION 功能详解
-
ora-00933 sql command not properly ended union all
-
Oracle查询提示 ORA-00933 SQL command not properly ended 原因排查-问题排查与解决
-
Oracle错误代码ORA-00933:UNION与ORDER BY的组合使用
-
【SQL】解决两个带order by查询进行union all时发生ORA-00933错误的方法
-
ORACLE错误:ORA-00933与Union all的关系
-
ORA-00933 UNION 与 ORDER BY
-
oracle union ora-00933