带排序的层次化查询

 

做AppendingFee时,每张单子会产生一张对冲单,对冲金额与原单相反.

主查询需要是按原单-原单排序,如果有对冲单的话 是原单-对冲单/原单的排序.

一开始用start with 和connect by nocycle/prior+伪列 level都没有好的办法解决.

 

想了个办法  呵呵,自己搞定了,主要就是用两个case搞定

create table test

(

col1 number,

col2 varchar2(20),

col3 number

);

 

insert into test

select 1,'ASDF','' from dual 

union all

select 2,'ASF','' from dual 

union all

select 3,'WE','2' from dual 

union all

select 4,'FGF','1' from dual 

union all

select 5,'BNM','' from dual 

union all

select 6,'678','' from dual 

union all

select 7,'CN','6' from dual 

union all

select 8,'NM','' from dual 

union all

select 9,'GGG','' from dual 

union all

select 10,'FFFF','8' from dual;

 

select * from test;

 

select col1 ap_id,col2 ap_content,

       col3 ap_hedge_id,

       orderid2 reverse_flag

from

(

select 

case when col3 is null then col1 else col3 end orderid,

case when col3 is null then 0 else 1 end orderid2,

col1,col2,col3

 from test

 ) a

 order by orderid desc,orderid2 asc;