데이터베이스 질문과 답변
안녕하세요...
고수분들의 도움이 필요하여 염치 불구하고 글을 등록합니다.
| 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
;
) 버튼을 클릭하여 작성 하시면 됩니다.