应该可以用lag或者lead函数处理,但是我奇怪你的1002帐期数据对应的上一帐期应该使1001帐期有数据才对,你给出的全是null,这个是不是有问题?
你看看下面的应该是不是你要的模式:
SQL> select * from test; MARK NAME
----- --------------------
88 1
84 2SQL>
SQL> select name name1, 1 ,
2 lead(name,1,0)over(partition by rid order by name) name2,
3 lead(,1,0)over(partition by rid order by name) 2 from (
4 select 1 rid,name, from test);NAME1 MARK1 NAME2 MARK2
-------------------- ----- ---------------------------------------- ----------
1 88 2 84
2 84 0 0
你看看下面的应该是不是你要的模式:
SQL> select * from test; MARK NAME
----- --------------------
88 1
84 2SQL>
SQL> select name name1, 1 ,
2 lead(name,1,0)over(partition by rid order by name) name2,
3 lead(,1,0)over(partition by rid order by name) 2 from (
4 select 1 rid,name, from test);NAME1 MARK1 NAME2 MARK2
-------------------- ----- ---------------------------------------- ----------
1 88 2 84
2 84 0 0
A.*
A.*
A.*
A.*
B.*
B.*
B.*
B.*
from
(
select
(
nvl(A.*)as A.*,
nvl(A.*)as A.*,
nvl(A.*)as A.*,
nvl(A.*)as A.*,
)
form *
union
select
(
nvl(B.*)as B.*,
nvl(B.*)as B.*,
nvl(B.*)as B.*,
nvl(B.*)as B.*,
)
form *)大致的结构就是这样的,你的条件给的不全,没法替你写完,依据结构自己写吧
from
(select process_type,acct_item_type_id,billing_cycle_id,sum(charge) from A where region_id=? group by process_type,acct_item_type_id,billing_cycle_id) b
full outer join
(select process_type,acct_item_type_id,billing_cycle_id,sum(charge) from A where region_id=? group by process_type,acct_item_type_id,billing_cycle_id) c
where b.process_type=c.process_type and b.acct_item_type_id=c.acct_item_type_id
and b.billing_cycle_id=c.billing_cycle_id
注:oracle9i支持outer join
from
(select process_type,acct_item_type_id,billing_cycle_id,sum(charge) from A where region_id=? group by process_type,acct_item_type_id,billing_cycle_id) b
full outer join
(select process_type,acct_item_type_id,billing_cycle_id,sum(charge) from A where region_id=? group by process_type,acct_item_type_id,billing_cycle_id) c
where b.process_type=c.process_type and b.acct_item_type_id=c.acct_item_type_id
and b.billing_cycle_id=c.billing_cycle_id+1