select col_1,col_2,col_3 from tb_test where status >= 0 order by status union all select col_1,col_2,col_3 from tb_test where status < 0 order by status;
--楼上的写法会报错 ORA-00933: SQL command not properly ended --具体看下面代码: 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 / 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
with t as ( select 1 num from dual union select 0 from dual union select 3 from dual union select -1 from dual union select 2 from dual )select * from t order by decode(num,-1,1,3,2,2,3,1,4,0,5) desc
select * from tb_test order by decode(status,'-1','9',status) -1变成最大的9,然后排序
with t as ( select 1 num from dual union select 0 from dual union select 3 from dual union select -1 from dual union select 2 from dual )select * from t order by decode(num,-1,9,num)0 1 2 3 -1
select col_1,col_2,col_3
from tb_test
where status >= 0
order by status
union all
select col_1,col_2,col_3
from tb_test
where status < 0
order by status;
--楼上的写法会报错 ORA-00933: SQL command not properly ended
--具体看下面代码:
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
/
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
with t as (
select 1 num from dual
union
select 0 from dual
union
select 3 from dual
union
select -1 from dual
union
select 2 from dual
)select * from t order by decode(num,-1,1,3,2,2,3,1,4,0,5) desc
-1变成最大的9,然后排序
with t as (
select 1 num from dual
union
select 0 from dual
union
select 3 from dual
union
select -1 from dual
union
select 2 from dual
)select * from t order by decode(num,-1,9,num)0
1
2
3
-1