Oracle DB 값 비교 관련 문의 1 9 1,125

by InsideCore [SQL Query] [2017.10.16 08:38:54]


안녕하세요...

고수분들의 도움이 필요하여 염치 불구하고 글을 등록합니다.

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)  칼럼 및 값을 추출하고 싶은데요.

간단하게 처리할 방법이 없을까요??

(최종적으로 알고 싶은 사항은 값이 다른 칼럼 및 차이 수량 입니다.)

 

고수님들의 조언 부탁 드립니다.

 

by 우리집아찌 [2017.10.16 09:08:31]

원하시는 결과물(출력양식?)은요?   


by InsideCore [2017.10.16 09:43:09]

차이나는 칼럼 정보와 값 정보를 알고 싶어요..

해당 칼럼 및 값을 이용하여 동일한 칼럼에 수량이 많은 쪽에서 수량 차감 할꺼거든요..


by 우리집아찌 [2017.10.16 09:53:01]

가로/세로 어떤 형식이 필요한가요?

1. 

O_NUMBER P_NUMBER ITEM_NO SEQ QTY S01M S01T S02M S02T S03M

2

_NUMBER P_NUMBER ITEM_NO SEQ QTY S01M
_NUMBER P_NUMBER ITEM_NO SEQ QTY S01T

 

 


by InsideCore [2017.10.16 10:06:57]

가로 형식으로 나타나면 제일 좋을것 같습니다.


by 마농 [2017.10.16 13:37:11]

막연하네요.
원본을 표로 보여주셨듯이, 결과도 표로 보여주세요.


by 우리집아찌 [2017.10.16 10:21:01]
-- 컬럼일부만 샘플링했습니다.
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

 


by 박군two [2017.10.16 10:33:45]
--단순무식한 방법으로 ...
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 으로 보이도록 하는것 맞죠?


by 우리집아찌 [2017.10.16 11:29:11]

A.와 B.의 JOIN 이 빠졌네요.

샘플데이터가 2개만 있으란 법은 없으니까요.

올려주신 쿼리 보니까 ROLLUP으로도 가능할듯 하네요.. ( 전 결과만 봐서 ) 

 

 


by 마농 [2017.10.16 13:44:06]
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입