求救啊！！！这段MySQL代码怎么改写成Oracle代码啊

[小张程序员]

SELECT
t.transaction_id,
MAX(IF ( rn = 1, t.plan_amount_total, NULL )) AS first_plan_amount,
MAX(IF ( rn = 1, t.fully_paid, NULL )) AS first_is_fully_paid,
MAX(IF ( rn = 2, t.plan_amount_total, NULL )) AS second_plan_amount,
MAX(IF ( rn = 2, t.plan_receipt_date, NULL ) ) AS second_receipt_date,
MAX(IF ( rn = 2, t.fully_paid, NULL )) AS second_is_fully_paid,
MAX(IF ( rn = 3, t.plan_amount_total, NULL )) AS third_plan_amount,
MAX(IF ( rn = 3, t.plan_receipt_date, NULL ) ) AS third_receipt_date,
MAX(IF ( rn = 3, t.fully_paid, NULL )) AS third_is_fully_paid,
MAX(IF ( rn = 4, t.plan_amount_total, NULL )) AS four_plan_amount,
MAX(IF ( rn = 4, t.plan_receipt_date, NULL ) ) AS four_receipt_date,
MAX(IF ( rn = 4, t.fully_paid, NULL )) AS four_is_fully_paid,
MAX(IF ( rn = 5, t.plan_amount_total, NULL )) AS fifth_plan_amount,
MAX(IF ( rn = 5, t.plan_receipt_date, NULL ) ) AS fifth_receipt_date,
MAX(IF ( rn = 5, t.fully_paid, NULL )) AS fifth_is_fully_paid
FROM
(SELECT
@r:= case when @transactionId = p1.transaction_id then @r + 1 else 1 end as rn,
p1.transaction_id AS transactionId,
p1.*
FROM
(SELECT
p.transaction_id,
p.plan_amount_total,
p.plan_receipt_date,
IF
( SUM( item.actual_amount ) = p.plan_amount_total, '是', '否' ) AS fully_paid
FROM
yxcw.fin_payment_plan p
INNER JOIN rep_param_project pp ON ( pp.job_id = p_report_log_id AND pp.project_num = p.project_num)
LEFT JOIN yxcw.fin_actual_received_item item ON item.is_deleted = 0
AND item.payment_plan_id = p.id
WHERE
1 = 1
AND p.fund_name_code NOT IN ('FIFT01001','FIFT01004')
AND p.fund_type_code IN ('FIFT01','FIFT02')
GROUP BY
p.id
ORDER BY
p.plan_receipt_date ASC,
p.id ASC ) p1,
(select @r:=0 ,@transactionId:='') b
ORDER BY
p1.transaction_id,
p1.plan_receipt_date
) t
GROUP BY
t.transaction_id

[nayi_224]

IF(rn = 1, t.plan_amount_total, NULL) ==》 decode(rn, 1, t.plan_amount_total) @r := case when @transactionId = p1.transaction_id then @r + 1 else 1 end as rn (select @r := 0, @transactionId := '') ==》 1 + sum(decode(p1.transaction_id, null, 1, 0)) over(order by p1.transaction_id, p1.plan_receipt_date)

[nayi_224]

引用 1 楼 nayi_224 的回复:

IF(rn = 1, t.plan_amount_total, NULL) ==》 decode(rn, 1, t.plan_amount_total) @r := case when @transactionId = p1.transaction_id then @r + 1 else 1 end as rn (select @r := 0, @transactionId := '') ==》 1 + sum(decode(p1.transaction_id, null, 1, 0)) over(order by p1.transaction_id, p1.plan_receipt_date)

第二段有点问题。。。 语法给你，自己往上套吧

with tab1 as (
select '' id, 1 ord from dual union all
select '' id,2 from dual union all
select '' id,3 from dual union all
select '1121' id,4 from dual union all
select '' id,5 from dual union all
select '' id,6 from dual union all
select '11122' id,7 from dual union all
select '' id,8 from dual 
)
, tab2 as (
select t1.*,
       sum(decode(t1.id, null, 0, 1)) over(order by ord) group_
  from tab1 t1
)
select t1.*,
       row_number() over(partition by t1.group_ order by t1.ord) rn
  from tab2 t1
;