아래와 같은 예제의 결과값을 얻고 싶은데 잘 안 되네요.
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는 오라클입니다.
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 ; http://gurubee.net/lecture/2837