oracle-order by 子句说明

1.对null值进行排序时，null 默认为最大值，指定Nulls first 则表示null值的记录将排在最前(不管是asc 还是desc)，指定Nulls last 则表示null值的记录将排在最后(不管是asc 还是desc)

select * from tmp_2 order by trim(aa) desc nulls first;
select * from tmp_2 order by trim(aa) asc  nulls first;
select * from tmp_2 order by trim(aa) desc nulls last;
select * from tmp_2 order by trim(aa) asc  nulls last;

2.select * from tmp_2 order by aa union select * from tmp_2;--ora-00933
 select * from tmp_2  union select * from tmp_2 order by aa;--ora-00904
Order by子句在整个select语句中的位置，始终位于最后

3.order by后可以跟：列名，列的别名，表达式，列出现在select关键字后的顺序(列号)；

 出现在order by子句之后的列，不一定出现在select子句中

select  a.aa||a.bb||a.cc,a.* from tmp_2 a order by 1 ;
select  a.aa||a.bb||a.cc "dddd cc",a.* from tmp_2 a order by "dddd cc" ;
select  a.aa||a.bb||a.cc dddd,a.* from tmp_2 a order by dddd ;
select  a.aa||a.bb||a.cc dddd,a.* from tmp_2 a order by "dddd" ;--ora-00904
select  a.aa||a.bb||a.cc "dddd",a.* from tmp_2 a order by dddd ;--ora-00904
select  a.aa||a.bb||a.cc "dddd",a.* from tmp_2 a order by "dddd" ;

4.

 select * from tmp_2  union select * from tmp_2  order by AA;--ora00904
 select * from tmp_2  union select * from tmp_2  order by 1;

 select  a.aa from tmp_2 a union  all select b.cc from tmp_2  border by   aa;