现在有一张交易表,tradetbl:
id | tranType | raiseAmt | tranState
1 | 02 | 1000.00 | 1
2 | 02 | 1000.00 | 2
3 | 02 | 1000.00 | 1
4 | 02 | 1000.00 | 3
5 | 02 | 1000.00 | 1tranState(交易状态): 2成功 3失败 1未明我想实现查询这张表的所有记录,让交易状态为失败和未明的记录显示在最前面。
请问在Oracle里该怎样些SQL语句?
id | tranType | raiseAmt | tranState
1 | 02 | 1000.00 | 1
2 | 02 | 1000.00 | 2
3 | 02 | 1000.00 | 1
4 | 02 | 1000.00 | 3
5 | 02 | 1000.00 | 1tranState(交易状态): 2成功 3失败 1未明我想实现查询这张表的所有记录,让交易状态为失败和未明的记录显示在最前面。
请问在Oracle里该怎样些SQL语句?
select '2' as id, '02' as tranType, '1000.00' as raiseAmt, '2' as tranState from dual union
select '3' as id, '02' as tranType, '1000.00' as raiseAmt, '1' as tranState from dual union
select '4' as id, '02' as tranType, '1000.00' as raiseAmt, '3' as tranState from dual union
select '5' as id, '02' as trantype, '1000.00' as raiseamt, '1' as transtate from dual
) select id, trantype, raiseamt, transtate
from a
order by decode(transtate, '1', '1', '3', '0', '2', '2');
(
select 1 a, 1000.00 b, 1 c from dual
union all
select 2 ,1000.00, 2 from dual
union all
select 3 , 1000.00, 1 from dual
union all
select 4 , 1000.00, 3 from dual
union all
select 5 , 1000.00,1 from dual
)select a,b,c from t order by mod(c,2) desc,a
--result:1 1000 1
3 1000 1
4 1000 3
5 1000 1
2 1000 2
decode(tranState, '3', '0', '1', '1', '2', '999', '50')
排序即可。
with t as
(
select 1 a, 1000.00 b, 1 c from dual
union all
select 2 ,1000.00, 2 from dual
union all
select 3 , 1000.00, 1 from dual
union all
select 4 , 1000.00, 3 from dual
union all
select 5 , 1000.00,1 from dual
)select a,b,c from t order by mod(c,2) desc,c desc我是这样处理的,也实现了同样的效果;
我也教你一个
ORDER BY DECODE(TRANSTATE, 2, NULL, TRANSTATE) DESC NULLS LAST
[SYS@myoracle] SQL>with a as (select '1' as id, '02' as trantype, '1000.00' as raiseamt, '1' as transtate from dual union
2 select '2' as id, '02' as tranType, '1000.00' as raiseAmt, '2' as tranState from dual union
3 select '3' as id, '02' as tranType, '1000.00' as raiseAmt, '1' as tranState from dual union
4 select '4' as id, '02' as tranType, '1000.00' as raiseAmt, '3' as tranState from dual union
5 select '5' as id, '02' as trantype, '1000.00' as raiseamt, '1' as transtate from dual
6 ) SELECT ID, TRANTYPE, RAISEAMT, TRANSTATE
7 FROM A
8 ORDER BY DECODE(TRANSTATE, 2, NULL, TRANSTATE) DESC NULLS LAST
9 ;I TR RAISEAM T
- -- ------- -
4 02 1000.00 3
5 02 1000.00 1
1 02 1000.00 1
3 02 1000.00 1
2 02 1000.00 2
--快,大家都来学习!
with t as(
select 1,02,1000.0,1 from dual
union all
select 2,02,1000.0,2 from dual
union all
select 3,02,1000.0,1 from dual
union all
select 4,02,1000.0, 1 from dual
union all
select 5,02,1000,0,1 from dual
)
---查询
select id,trantype,raiseamt,transtate
from a
order by decode(transtate,2,null,transtate) desc