[20140812]union all与order by.txt

--昨天同事问一个sql的问题,关于union all与order by的问题.做一个简单的记录:

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t1 as select rownum id from dual connect by level<=3;
Table created.

SCOTT@test> create table t2 as select 7-rownum id from dual connect by level<=3;
Table created.

SCOTT@test> create table t3 as select * from t1 where 1=2;
Table created.

insert into t3 (id ) values (8);
insert into t3 (id ) values (9);
insert into t3 (id ) values (7);
commit;

execute dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);
execute dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);
execute dbms_stats.gather_table_stats(user,'t3',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);

如果写成如下:

select * from t1
union all
select * from t2 order by 1
union all
select * from t3;

union all
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

--许多人认为union all里面不能使用order by.但是如果写成如下,没有问题:
select * from t1
union all
select * from t2
union all
select * from t3 order by 1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

--实际上order by在union all中对整个结果集排序,这样仅仅出现在最后.如果想对中间的表t2排序输出.
--只能这样写:

select * from t1
union all
select * from (select * from t2 order by 1)
union all
select * from t3 ;

        ID
----------
         1
         2
         3
         4
         5
         6
         8
         9
         7

--或者写成如下:

with a as (select * from t2 order by 1)
select * from t1
union all
select * from a
union all
select * from t3 ;