Oracle Union All 排序
在oracle中使用union all或者 union 对两个结果集进行并集操作时，如果需要对查询结果集进行排序时，不能直接在后面加order by + 表字段 来排序
例如： 在oracle的soctt用户中emp表对部门号为20和30的员工进行并集操作：
SQL> select * from emp where deptno = 20 union all select * from emp where deptno =30 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择11行。
先将结果按照hidredate 进行排序，直接加order by hiredate 是错误的
SQL> select * from emp where deptno = 20 union all select * from emp where deptno =30 order by hiredate;
select * from emp where deptno =30 order by HIREDATE
*
第 2 行出现错误:
ORA-00904: "HIREDATE": 标识符无效
解决办法一般有三种：
方法一： 先将结果集进行包装，包装过后在按照hiredate排序：
SQL> select * from (
select * from emp where deptno = 20 union all select * from emp where deptno =30
)order by HIREDATE ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 已选择11行。
方法二：单独对表进行排序，排序后在执行并集操作：
SQL> select * from ( select * from emp where deptno = 20 order by hiredate) union all select * from ( select * from emp where deptno =30 order by hiredate ); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7900 JAMES CLERK 7698 03-12月-81 950 30 已选择11行。
方法三： 直接加order by + 字段在结果集中的序号，此例子中hiredate在结果集的第五列，则为 order by 5 即可：
SQL> select * from emp where deptno = 20 union all select * from emp where deptno =30 order by 5 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
已选择11行。
