아래와 같은 예제의 결과값을 얻고 싶은데 잘 안 되네요.
EX1)
A씨 -10,000,000
B씨 5,000,000
C씨 3,000,000
D씨 3,000,000
E씨 -1,000,000
결과 표
B씨 -> A씨 5,000,000
C씨 -> A씨 3,000,000
D씨 -> A씨 2,000,000
D씨 -> E씨 1,000,000
EX2)
A씨 10,000,000
B씨 -5,000,000
C씨 -3,000,000
D씨 -3,000,000
E씨 2,000,000
F씨 -1,000,000
결과 표
A씨 -> B씨 5,000,000
A씨 -> C씨 3,000,000
A씨 -> D씨 2,000,000
E씨 -> D씨 1,000,000
E씨 -> F씨 1,000,000
DB는 오라클입니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | WITH t AS ( SELECT 'EX1' gb, 'A씨' nm, -10000000 amt FROM dual UNION ALL SELECT 'EX1' , 'B씨' , 5000000 FROM dual UNION ALL SELECT 'EX1' , 'C씨' , 3000000 FROM dual UNION ALL SELECT 'EX1' , 'D씨' , 3000000 FROM dual UNION ALL SELECT 'EX1' , 'E씨' , -1000000 FROM dual UNION ALL SELECT 'EX2' , 'A씨' , 10000000 FROM dual UNION ALL SELECT 'EX2' , 'B씨' , -5000000 FROM dual UNION ALL SELECT 'EX2' , 'C씨' , -3000000 FROM dual UNION ALL SELECT 'EX2' , 'D씨' , -3000000 FROM dual UNION ALL SELECT 'EX2' , 'E씨' , 1000000 FROM dual ) , t1 AS ( SELECT gb , nm , ABS (amt) amt , SIGN(amt) s , SUM ( ABS (amt)) OVER(PARTITION BY gb, SIGN(amt) ORDER BY nm) sum_amt FROM t ) SELECT a.gb , a.nm nm_a , b.nm nm_b , LEAST( a.amt , b.amt , b.sum_amt - a.sum_amt + a.amt , a.sum_amt - b.sum_amt + b.amt ) amt FROM t1 a , t1 b WHERE a.s = +1 AND b.s = -1 AND a.gb = b.gb AND a.sum_amt - a.amt < b.sum_amt AND b.sum_amt - b.amt < a.sum_amt ORDER BY gb, nm_a, nm_b ; |