SQL> SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码 FROM emp
2 order by empno
3 UNION ALL
4 SELECT deptno AS 编码, dname AS 名称, NULL AS 上级编码 FROM dept
5 order by deptno;
UNION ALL
*
第 3 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码 FROM emp
2 /*order by empno*/
3 UNION ALL
4 SELECT deptno AS 编码, dname AS 名称, NULL AS 上级编码 FROM dept
5 order by 1;
编码 名称 上级编码
---------- -------------- ----------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
编码 名称 上级编码
---------- -------------- ----------
7788 SCOTT 7566
7839 KING 10
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
已选择18行。
SQL> SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码 FROM emp
2 /*order by empno*/
3 UNION ALL
4 SELECT deptno AS 编码, dname AS 名称, NULL AS 上级编码 FROM dept
5 order by 编码;
编码 名称 上级编码
---------- -------------- ----------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
编码 名称 上级编码
---------- -------------- ----------
7788 SCOTT 7566
7839 KING 10
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
已选择18行。
1.order by 只能放在最后
2.排序可以用别名和列位置