扩展这个问题的答案: 支付distrubution oracle sql查询
您仍然可以使用SQL MODEL 条款。在此版本中,您需要为每个不同的单独计算 account_num 。你可以使用 PARTITION SQL的关键字 MODEL 要分区的子句 account_num 。
MODEL
account_num
PARTITION
像这样(请参阅SQL注释以获得逐步说明):
-- Set up test data (since I don't have your table) WITH inv_raw (item_order, inv_amount, partial_pmt_allowed, account_num, cr_amt) AS ( SELECT 1, 1256, 'Y', 12, 1000 FROM DUAL UNION ALL SELECT 2, 1134, 'Y', 12, 1000 FROM DUAL UNION ALL SELECT 3, 800, 'Y', 13, 1200 FROM DUAL UNION ALL SELECT 4, 200, 'N',13, 1200 FROM DUAL UNION ALL SELECT 5, 156, 'N',13, 1200 FROM DUAL), -- Ensure that the column we are ordering by is densely populated inv_dense (dense_item_order, item_order, inv_amount, partial_pmt_allowed, account_num, cr_amt) AS ( SELECT DENSE_RANK() OVER ( PARTITION BY account_num ORDER BY item_order ), item_order, inv_amount, partial_pmt_allowed, account_num, cr_amt FROM inv_raw ) -- Give us a way to input the payment amount --param AS ( SELECT 1100 p_payment_amount FROM DUAL ) -- The actual query starts here SELECT account_num, item_order, inv_amount, partial_pmt_allowed, applied dist_amount, remaining_out balance_amt, cr_amt FROM inv_dense MODEL -- We want a completely separate calculation for each distinct account_num PARTITION BY ( account_num ) -- We'll output one row for each value of dense_item_order. -- We made item_order "dense" so we can do things like CV()-1 to get the -- previous row's values. DIMENSION BY ( dense_item_order ) MEASURES ( cr_amt, item_order, inv_amount, partial_pmt_allowed, 0 applied, 0 remaining_in, 0 remaining_out ) RULES AUTOMATIC ORDER ( -- The amount carried into the first row is the payment amount remaining_in[1] = cr_amt[1], -- The amount carried into subsequent rows is the amount we carried out of the prior row remaining_in[dense_item_order > 1] = remaining_out[CV()-1], -- The amount applied depends on whether the amount remaining can cover the invoice -- and whether partial payments are allowed applied[ANY] = CASE WHEN remaining_in[CV()] >= inv_amount[CV()] OR partial_pmt_allowed[CV()] = 'Y' THEN LEAST(inv_amount[CV()], remaining_in[CV()]) ELSE 0 END, -- The amount we carry out is the amount we brought in minus what we applied remaining_out[ANY] = remaining_in[CV()] - applied[CV()] ) ORDER BY account_num, item_order;