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
uj5u.com熱心網友回復：
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)
uj5u.com熱心網友回復：
第二段有點問題。。。
語法給你，自己往上套吧
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
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/13055.html
標籤：開發
上一篇：ORA-12569