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排序和分組子句