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排序和分组子句 Oracle 按照日期先后顺序连接字符串Oracle Pipelined Table Functions简介相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（1）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任