[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 levelTable created.
SCOTT@test> create table t2 as select 7-rownum id from dual connect by levelTable 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 ;