oracle union all和order by一起使用

//有时候，我们会将进过排序(order by)后的结果集与     

//其他经过排序的结果集进行合并(union or union all)     

//比如：     

select * from tb where length(id)=5 order by id desc    

union all    

select * from tb where length(id)=10 order by id asc    

//通常情况下，上面的查询将会得到下面的错误提示:     

//ORA-00933: SQL command not properly ended     

//错误指向union关键字这里     

//下面我们来看一个具体的实例:     

//     

create table t as    

select 'china' col_1,'america' col_2,'canada' col_3,-1 status from dual union all    

select '花生','瓜子','绿豆',0 from dual union all    

select '牙膏','牙刷','杯子',3 from dual union all    

select '芍药','牡丹','月季',1 from dual union all    

select '优乐美','香飘飘','炸鸡',2 from dual    

/    

//需求：     

//有如上表t，status字段的取值范围:[-1,3]     

//我们想要做的是，按照这样的方式排序0,1,2,3,-1     

//     

//解法：     

//更具题义，我们需要将status分为两个区域(>0 和<0)     

//然后分别对每一个区域内的数据进行order by排序     

//于是有下面的查询     

select col_1,col_2,col_3,status    

from t    

where status >= 0     

order by status  --1    

union    

select col_1,col_2,col_3,status    

from t    

where status < 0    

order by status  --2    

/    

//不幸的是，正如刚刚开始时我提示的一样，我们得到了下面的错误提示:     

//ORA-00933: SQL command not properly ended     

//如果将第一个select语句的order by子句去掉，得到的又不是我们想要的结果     

//如果将两个排序子句都去掉的话，虽然按照status为正负数分开了，但是没有排序     

//下面我们来看看正确的答案吧!   

//解法一:   

select * from (    

       select col_1,col_2,col_3,status    

       from t    

       where status >= 0    

       order by status)    

union all    

select * from (    

       select col_1,col_2,col_3,status    

       from t    

       where status < 0    

       order by status)    

/    

COL_1  COL_2   COL_3      STATUS    

------ ------- ------ ----------    

花生   瓜子    绿豆            0    

芍药   牡丹    月季            1    

优乐美 香飘飘  炸鸡            2    

牙膏   牙刷    杯子            3    

china  america canada         -1   

//解法二:   

select * from t   

order by   

      decode(status,  

             -1,1,  

             3,2,  

             2,3,  

             1,4,  

             0,5) desc  

/  

//这可是一个很妙的排序，本人首次看到在order by语句中可以使用decode()函数来排序   

//同理，我们也可以使用case语句来排序:   

//解法三:   

select * from t   

order by   

      case status  

      when -1 then 5  

      when 3 then 4   

      when 2 then 3   

      when 1 then 2   

      else 1  

      end   

/  

//union 和union all中都支持order by和group by排序和分组子句  

原帖:http://topic.csdn.net/u/20110504/23/eb02715e-f9ad-40ca-9716-b8cc4a3e36e4.html?seed=832281601&r=73131996#r_73131996