문자추출+구분자+기존값합치기 질문입니다! 1 6 1,069

by 재래개 [SQL Query] [2016.05.22 08:32:05]


 안녕하세요. 평화로운 주말의 아침입니다.

 구루비 회원분들의 도움을 얻고자 이렇게 아침 일찍 문의를 드립니다.. 

2번 테이블 ITEM_FABRIC1 컬럼의 값 A100,B200,C300 을 문자만 추출(A,B,C)하여 3번 테이블의 FABRIC_NM으로(껍질,씨앗,잎) 바꾼 후 

2번테이블의 문자 외의 값 100,200,300 을 3번 테이블로 변환한 값(껍질,씨앗,잎) + 기존값(100,200,300) -> 껍질100,씨앗200,잎300 으로 변환해야 합니다..

 

문자추출 후 3번테이블 조인해서 껍질,씨앗,잎 으로 바꾸는것까지는 되나 껍질100,씨앗200,잎300 으로 변환하는게 어렵습니다..  

 

 

총 3개의 테이블이 있습니다. 

1번과 2번 테이블의 KEY는 ITEM_CD(사과,감자,배) 입니다.

 

1)

ITEM_CD ITEM_SIZE ITEM_QTY
사과 10
사과 9
사과 8
감자 10
감자 9
감자 8
딸기 10
딸기 9
딸기 8
 SELECT '사과' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
 SELECT '사과' ITEM_CD, '중' ITEM_SIZE, '9' ITEM_QTY FROM DUAL UNION ALL
 SELECT '사과' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL ITEM


 

2)

ITEM_CD ITEM_FABRIC1 ITEM_FABRIC2
사과 A50,B30,C20 D100
감자 A50,B50 D50,E50
딸기 A10,B90 D10,E90
 SELECT '사과' ITEM_CD, 'A50,B30,C20' ITEM_FABRIC1, 'D100'    ITEM_FABRIC2 FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, 'A50,B50'     ITEM_FABRIC1, 'D50,E50' ITEM_FABRIC2 FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, 'A30,B20,C50' ITEM_FABRIC1, 'D10,E90' ITEM_FABRIC2 FROM DUAL ITEM_FABRIC

3)

FABRIC_CD FABRIC_NM 
A 껍질
B 씨앗
C
D 단백질
E 비타민
 SELECT 'A' FABRIC_CD, '껍질'  FABRIC_NM FROM DUAL UNION ALL
 SELECT 'B' FABRIC_CD, '씨앗'  FABRIC_NM FROM DUAL UNION ALL
 SELECT 'C' FABRIC_CD, '잎'    FABRIC_NM FROM DUAL UNION ALL
 SELECT 'D' FABRIC_CD, '단백질' FABRIC_NM FROM DUAL UNION ALL
 SELECT 'E' FABRIC_CD, '비타민' FABRIC_NM FROM DUAL FABRIC

 

result :

 

ITEM_CD ITEM_SIZE ITEM_FABRIC1 ITEM_FABRIC2 ITEM_QTY
사과 껍질50,씨앗30,잎20 단백질100 10
사과 껍질50,씨앗30,잎20 단백질100 9
사과 껍질50,씨앗30,잎20 단백질100 8
감자 껍질50,씨앗50 단백질50,비타민50 10
감자 껍질50,씨앗50 단백질50,비타민50 9
감자 껍질50,씨앗50 단백질50,비타민50 8
딸기 껍질10,씨앗90 단백질10,비타민90 10
딸기 껍질10,씨앗90 단백질10,비타민90 9
딸기 껍질10,씨앗90 단백질10,비타민90 8

 

by jkson [2016.05.23 08:29:11]
with ITEM as
(
SELECT '사과' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '중' ITEM_SIZE, '9' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL 
)
, ITEM_FABRIC as
(
SELECT '사과' ITEM_CD, 'A50,B30,C20' ITEM_FABRIC1, 'D100'    ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, 'A50,B50'     ITEM_FABRIC1, 'D50,E50' ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, 'A30,B20,C50' ITEM_FABRIC1, 'D10,E90' ITEM_FABRIC2 FROM DUAL 
)
, FABRIC as
(
SELECT 'A' FABRIC_CD, '껍질'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'B' FABRIC_CD, '씨앗'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'C' FABRIC_CD, '잎'    FABRIC_NM FROM DUAL UNION ALL
SELECT 'D' FABRIC_CD, '단백질' FABRIC_NM FROM DUAL UNION ALL
SELECT 'E' FABRIC_CD, '비타민' FABRIC_NM FROM DUAL 
)
SELECT B.ITEM_CD
     , B.ITEM_SIZE
     , A.ITEM_FABRIC1
     , A.ITEM_FABRIC2
     , B.ITEM_QTY
  FROM (SELECT ITEM_CD
             , LISTAGG(ITEM_FABRIC1, ',') WITHIN GROUP (ORDER BY LV) ITEM_FABRIC1
             , LISTAGG(ITEM_FABRIC2, ',') WITHIN GROUP (ORDER BY LV) ITEM_FABRIC2
          FROM (SELECT A.ITEM_CD
                     , B.FABRIC_NM || SUBSTR(ITEM_FABRIC1, 2) ITEM_FABRIC1
                     , C.FABRIC_NM || SUBSTR(ITEM_FABRIC2, 2) ITEM_FABRIC2
                     , LV LV
                  FROM (SELECT ITEM_CD
                             , REGEXP_SUBSTR(ITEM_FABRIC1, '[^,]+', 1, LV) ITEM_FABRIC1
                             , REGEXP_SUBSTR(ITEM_FABRIC2, '[^,]+', 1, LV) ITEM_FABRIC2
                             , LV
                          FROM ITEM_FABRIC A, (SELECT LEVEL LV
                                                 FROM DUAL
                                               CONNECT BY LEVEL < 20)
                         WHERE LV <= GREATEST(REGEXP_COUNT(ITEM_FABRIC1, ','), REGEXP_COUNT(ITEM_FABRIC1, ',')) + 1) A
                     , FABRIC B
                     , FABRIC C
                 WHERE SUBSTR(A.ITEM_FABRIC1, 1, 1) = B.FABRIC_CD(+)
                   AND SUBSTR(A.ITEM_FABRIC2, 1, 1) = C.FABRIC_CD(+))
        GROUP BY ITEM_CD) A
     , ITEM B
 WHERE B.ITEM_CD = A.ITEM_CD

만들고 보니 복잡...;;


by 재래개 [2016.05.23 10:22:44]

jkson 님 감사합니다!


by 우리집아찌 [2016.05.23 09:18:41]
WITH ITEM AS (
SELECT '사과' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '중' ITEM_SIZE, '9' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL 
) , ITEM_FABRIC AS (
SELECT '사과' ITEM_CD, 'A50,B30,C20' ITEM_FABRIC1, 'D100'    ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, 'A50,B50'     ITEM_FABRIC1, 'D50,E50' ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, 'A30,B20,C50' ITEM_FABRIC1, 'D10,E90' ITEM_FABRIC2 FROM DUAL 
) , FABRIC AS (
SELECT 'A' FABRIC_CD, '껍질'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'B' FABRIC_CD, '씨앗'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'C' FABRIC_CD, '잎'    FABRIC_NM FROM DUAL UNION ALL
SELECT 'D' FABRIC_CD, '단백질' FABRIC_NM FROM DUAL UNION ALL
SELECT 'E' FABRIC_CD, '비타민' FABRIC_NM FROM DUAL 
) 

SELECT T1.ITEM_CD , T1.ITEM_SIZE , MAX(FABRIC1) FABRIC1 , MAX(FABRIC2) FABRIC2, T1.ITEM_QTY 
  FROM ITEM T1
     , ( SELECT ITEM_CD
             , GB
             , DECODE(GB,1,LISTAGG(VAL_NM, ',') WITHIN GROUP(ORDER BY LV)) FABRIC1 
             , DECODE(GB,2,LISTAGG(VAL_NM, ',') WITHIN GROUP(ORDER BY LV)) FABRIC2
          FROM
             (SELECT ITEM_CD 
                   , CASE WHEN SUBSTR(VAL,1,1) IN ( 'A', 'B' , 'C' ) THEN 1 ELSE 2 END GB
                   , REPLACE(VAL,B.FABRIC_CD,B.FABRIC_NM) VAL_NM
                   ,LV
                FROM (
                      SELECT ITEM_CD 
                            ,LV
                            ,REGEXP_SUBSTR(ITEM_FABRIC1||','||ITEM_FABRIC2,'[^,]+',1,LV) VAL
                        FROM ITEM_FABRIC AA
                          , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 5 )           
                      ) A
                      , FABRIC B
              WHERE SUBSTR(VAL,1,1) = B.FABRIC_CD
             )
        GROUP BY ITEM_CD, GB
      ) T2
 WHERE T1.ITEM_CD = T2.ITEM_CD
 GROUP BY T1.ITEM_CD , T1.ITEM_SIZE , T1.ITEM_QTY 
 ORDER BY DECODE(T1.ITEM_CD,'사과',1,'감자',2,3) , T1.ITEM_SIZE

 


by 재래개 [2016.05.23 10:22:57]

우리집아찌 님 감사합니다!!


by 마농 [2016.05.23 09:32:47]
SELECT a.item_cd, a.item_size, a.item_qty
     , LISTAGG(
       REPLACE(REGEXP_SUBSTR(b.item_fabric1, c.fabric_cd||'[^,]+'), c.fabric_cd, c.fabric_nm)
       , ',') WITHIN GROUP(ORDER BY INSTR(b.item_fabric1, c.fabric_cd)) item_fabric1
     , LISTAGG(
       REPLACE(REGEXP_SUBSTR(b.item_fabric2, c.fabric_cd||'[^,]+'), c.fabric_cd, c.fabric_nm)
       , ',') WITHIN GROUP(ORDER BY INSTR(b.item_fabric2, c.fabric_cd)) item_fabric2
  FROM item        a
     , item_fabric b
     , fabric      c
 WHERE a.item_cd = b.item_cd
   AND INSTR(b.item_fabric1 || b.item_fabric2, c.fabric_cd) > 0
 GROUP BY a.item_cd, a.item_size, a.item_qty
 ORDER BY a.item_cd, INSTR('대중소', a.item_size)
;

 


by 재래개 [2016.05.23 10:23:09]

마농 님 감사합니다!!!

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