안녕하세요...
고수분들의 도움이 필요하여 염치 불구하고 글을 등록합니다.
O_NUMBER | P_NUMBER | ITEM_NO | SEQ | QTY | S01M | S01T | S02M | S02T | S03M | S03T | S04M | S04T | S05M | S05T | S06M | S06T | S07M | S07T | S08M | S08T | S09M | S09T | S10M | S10T | S11M | S11T | S12M | S12T | S13M | S13T | S14M |
313050 | 4505040009 | 150 | 1 | 808 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 36 | 36 | 203 | 0 | 59 | 0 | 328 | 0 | 64 | 41 | 5 | 36 | |||
313050 | 4505040009 | 150 | 2 | 1956 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 228 | 198 | 390 | 294 | 486 | 360 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
위와 같이 Table에 값이 존재할 경우
SEQ 1번과 2번에 대해서 값을 비교하는 SQL를 만들고자 합니다.
서로의 값을 비교하여 차이 (SEQ 1 - SEQ2) 칼럼 및 값을 추출하고 싶은데요.
간단하게 처리할 방법이 없을까요??
(최종적으로 알고 싶은 사항은 값이 다른 칼럼 및 차이 수량 입니다.)
고수님들의 조언 부탁 드립니다.
-- 컬럼일부만 샘플링했습니다. WITH T (O_NUMBER,P_NUMBER,ITEM_NO,SEQ,QTY,S01M,S01T,S02M,S02T,S03M,S04T) AS ( SELECT 313050 , 4505040009 , 150 , 1 , 808 , 0 , 0 , 0 , 0 , 0 , 0 FROM DUAL UNION ALL SELECT 313050 , 4505040009 , 150 , 2 , 1956 , 1 , 2 , 3 , 4 , 5 , 6 FROM DUAL ) SELECT O_NUMBER,P_NUMBER,ITEM_NO , ABS(SUM(S01M * DECODE(SEQ,1,1,-1))) AS S01M , ABS(SUM(S01T * DECODE(SEQ,1,1,-1))) AS S01T , ABS(SUM(S02M * DECODE(SEQ,1,1,-1))) AS S02M , ABS(SUM(S02T * DECODE(SEQ,1,1,-1))) AS S02T , ABS(SUM(S03M * DECODE(SEQ,1,1,-1))) AS S03M , ABS(SUM(S04T * DECODE(SEQ,1,1,-1))) AS S04T FROM T GROUP BY O_NUMBER,P_NUMBER,ITEM_NO
--단순무식한 방법으로 ... WITH T (O_NUMBER, P_NUMBER, ITEM_NO, SEQ, QTY, S01M, S01T, S02M, S02T, S03M, S03T, S04M, S04T, S05M, S05T, S06M, S06T, S07M, S07T, S08M, S08T, S09M, S09T, S10M, S10T, S11M, S11T, S12M, S12T, S13M, S13T, S14M) AS ( SELECT '313050', '4505040009', '150', '1', '808', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 36, 36, 203, 0, 59, 0, 328, 0, 64, NULL, 41, NULL, 5, NULL, 36 FROM DUAL UNION ALL SELECT '313050', '4505040009', '150', '2', '1956', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 228, 198, 390, 294, 486, 360, 0, 0, 0, 0, 0, 0, 0 FROM DUAL ) SELECT * FROM T UNION ALL SELECT 'O_NUMBER', 'P_NUMBER', 'ITEM_NO', 'SEQ', 'QTY', ABS(NVL(A.S01M, 0) - NVL(B.S01M, 0)) AS S01M, ABS(NVL(A.S01T, 0) - NVL(B.S01T, 0)) AS S01T, ABS(NVL(A.S02M, 0) - NVL(B.S02M, 0)) AS S02M, ABS(NVL(A.S02T, 0) - NVL(B.S02T, 0)) AS S02T, ABS(NVL(A.S03M, 0) - NVL(B.S03M, 0)) AS S03M, ABS(NVL(A.S03T, 0) - NVL(B.S03T, 0)) AS S03T, ABS(NVL(A.S04M, 0) - NVL(B.S04M, 0)) AS S04M, ABS(NVL(A.S04T, 0) - NVL(B.S04T, 0)) AS S04T, ABS(NVL(A.S05M, 0) - NVL(B.S05M, 0)) AS S05M, ABS(NVL(A.S05T, 0) - NVL(B.S05T, 0)) AS S05T, ABS(NVL(A.S06M, 0) - NVL(B.S06M, 0)) AS S06M, ABS(NVL(A.S06T, 0) - NVL(B.S06T, 0)) AS S06T, ABS(NVL(A.S07M, 0) - NVL(B.S07M, 0)) AS S07M, ABS(NVL(A.S07T, 0) - NVL(B.S07T, 0)) AS S07T, ABS(NVL(A.S08M, 0) - NVL(B.S08M, 0)) AS S08M, ABS(NVL(A.S08T, 0) - NVL(B.S08T, 0)) AS S08T, ABS(NVL(A.S09M, 0) - NVL(B.S09M, 0)) AS S09M, ABS(NVL(A.S09T, 0) - NVL(B.S09T, 0)) AS S09T, ABS(NVL(A.S10M, 0) - NVL(B.S10M, 0)) AS S10M, ABS(NVL(A.S10T, 0) - NVL(B.S10T, 0)) AS S10T, ABS(NVL(A.S11M, 0) - NVL(B.S11M, 0)) AS S11M, ABS(NVL(A.S11T, 0) - NVL(B.S11T, 0)) AS S11T, ABS(NVL(A.S12M, 0) - NVL(B.S12M, 0)) AS S12M, ABS(NVL(A.S12T, 0) - NVL(B.S12T, 0)) AS S12T, ABS(NVL(A.S13M, 0) - NVL(B.S13M, 0)) AS S13M, ABS(NVL(A.S13T, 0) - NVL(B.S13T, 0)) AS S13T, ABS(NVL(A.S14M, 0) - NVL(B.S14M, 0)) AS S14M FROM (SELECT * FROM T WHERE T.SEQ = 1) A, (SELECT * FROM T WHERE T.SEQ = 2) B ;
SEQ 1 - SEQ2 값이 음수이면 SEQ2 - SEQ1 으로 보이도록 하는것 맞죠?
WITH t ( o_number, p_number, item_no, seq , qty , s01m, s01t, s02m, s02t, s03m, s03t, s04m, s04t, s05m, s05t, s06m, s06t, s07m, s07t , s08m, s08t, s09m, s09t, s10m, s10t, s11m, s11t, s12m, s12t, s13m, s13t, s14m, s14t ) AS ( SELECT 313050, 4505040009, 150, 1, 808 , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 36, 36 , 203, 0, 59, 0, 328, 0, 64, null, 41, null, 5, null, 36, 0 FROM dual UNION ALL SELECT 313050, 4505040009, 150, 2, 1956 , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 , 228, 198, 390, 294, 486, 360, 0, 0, 0, 0, 0, 0, 0, 0 FROM dual ) SELECT o_number, p_number, item_no , c, v1, v2 , ABS(NVL(v1, 0) - NVL(v2, 0)) v3 FROM t UNPIVOT ( v FOR c IN ( qty , s01m, s01t, s02m, s02t, s03m, s03t, s04m, s04t, s05m, s05t, s06m, s06t, s07m, s07t , s08m, s08t, s09m, s09t, s10m, s10t, s11m, s11t, s12m, s12t, s13m, s13t, s14m, s14t ) ) PIVOT (MIN(v) FOR seq IN (1 v1, 2 v2)) WHERE NVL(v1, 0) != NVL(v2, 0) ORDER BY o_number, p_number, item_no, c ;