Oracle union all和order by一起使用

發布時間: 2012/8/21 17:08:45

//有時候，我們會將進過排序(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排序和分組子句