集合操作中ORDER BY的列需要在SELECT中列出

在集合操作中，ORDER BY 中的列，一定要在SELECT列表中选出，而且还不能采用用SELECT *的方法，否则会报ORA-00904错误。

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 c
         2

已选择2行。

SQL> select * from t2;

        ID NAME
---------- --------------------
         1 a
         2 a
         3 r
         4 e
        10

已选择5行。

SQL> select * from t1
  2  union
  3  select * from t2
  4  order by id desc;
order by id desc
         *
第 4 行出现错误:
ORA-00904: "ID": invalid identifier

即使SELECT *包含了ID列，ORDER BY 也会报错。

SQL> select id,name from t1
  2  union
  3  select * from t2
  4  order by id desc;

        ID NAME
---------- --------------------
        10
         4 e
         3 r
         2 a
         2
         1 a
         1 c

已选择7行。

 

SQL> select name from t1
  2  union
  3  select name from t2
  4  order by id desc;
order by id desc
         *
第 4 行出现错误:
ORA-00904: "ID": invalid identifier

SQL> select name from t1
  2  union
  3  select name from t2
  4  order by name desc;

NAME
--------------------

r
e
c
a

已选择5行。

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production

已选择5行。