DATE | JOB | QTY | VERSION |
201310 | A | 50 | BASE |
201310 | A | 50 | BASE |
201310 | A | 100 | 20131016_002 |
201310 | A | 100 | 20131016_002 |
201310 | B | 50 | BASE |
201310 | B | 50 | BASE |
201310 | B | 100 | 20131016_002 |
201310 | B | 100 | 20131016_002 |
201310 | B | 100 | 20131016_002 |
201310 | C | 50 | BASE |
201310 | C | 50 | BASE |
201310 | C | 100 | 20131016_002 |
201310 | C | 100 | 20131016_002 |
201310 | C | 100 | 20131016_002 |
201310 | D | 50 | BASE |
201310 | D | 50 | BASE |
DATE | JOB | QTY | VERSION |
201310 | A | 200 | 20131016_002 |
201310 | B | 300 | 20131016_002 |
201310 | C | 300 | 20131016_002 |
201310 | D | 100 | BASE |
-- 항상 답에만 맞춤 WITH T ( DT ,JOB ,QTY ,VERSION) AS ( SELECT '201310','A',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','A',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','A',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','A',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','B',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','B',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','B',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','B',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','B',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','C',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','C',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','C',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','C',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','C',100,'20131016_002' FROM DUAL UNION ALL SELECT '201310','D',50 ,'BASE' FROM DUAL UNION ALL SELECT '201310','D',50 ,'BASE' FROM DUAL ) SELECT DT , JOB , DECODE(ETC_SUM,0,BASE_SUM,ETC_SUM) QTY , DECODE(ETC_SUM,0,'BASE',VERSION) VERSION FROM (SELECT DT , JOB , SUM(DECODE(VERSION,'BASE',QTY )) BASE_SUM , SUM(DECODE(VERSION,'BASE',0,QTY ))ETC_SUM , MAX(DECODE(VERSION,'BASE','',VERSION)) VERSION FROM T GROUP BY DT , JOB ) ORDER BY DT , JOB
WITH t AS ( SELECT '201310' dat, 'A' job, 50 qty, 'BASE' version FROM dual UNION ALL SELECT '201310', 'A', 50, 'BASE' FROM dual UNION ALL SELECT '201310', 'A', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'A', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'B', 50, 'BASE' FROM dual UNION ALL SELECT '201310', 'B', 50, 'BASE' FROM dual UNION ALL SELECT '201310', 'B', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'B', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'B', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'C', 50, 'BASE' FROM dual UNION ALL SELECT '201310', 'C', 50, 'BASE' FROM dual UNION ALL SELECT '201310', 'C', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'C', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'C', 100, '20131016_002' FROM dual UNION ALL SELECT '201310', 'D', 50, 'BASE' FROM dual UNION ALL SELECT '201310', 'D', 50, 'BASE' FROM dual ) SELECT dat , job , SUM(qty) qty , MAX(version) version FROM (SELECT dat, job, qty, version , RANK() OVER(PARTITION BY dat, job ORDER BY DECODE(version, 'BASE', 1, 0)) rk FROM t ) WHERE rk = 1 GROUP BY dat, job ORDER BY dat, job ;