Oracle union all和order by一起使用

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

?

laizi:http://hi.baidu.com/leeyoungtek/blog/item/8b5416da768e76c5b6fd4890.html