3개 TABLE JOIN 해서 TREE 구조로 표현하려고 합니다 0 3 1,046

by Kaiger TREE JOIN [2015.04.02 17:53:23]


WITH t03 AS
(
SELECT '1500' plant, '001' fId, '설비1' flNm, 'f10' fcType, '1' flType FROM dual union all
SELECT '1500' plant, '002' fId, '설비2' flNm, 'f10' fcType, '2' flType FROM dual union all
SELECT '1500' plant, '003' fId, '설비3' flNm, 'f20' fcType, '1' flType FROM dual union all
SELECT '1500' plant, '004' fId, '설비4' flNm, 'f20' fcType, '2' flType FROM dual union all
SELECT '1500' plant, '005' fId, '설비5' flNm, 'f30' fcType, '1' flType FROM dual
)

WITH t01 AS
(
SELECT '1500' plant, '001' fId, 'AAA-0001' eqId, '10' eqType FROM dual union all
SELECT '1500' plant, '002' fId, 'AAA-0002' eqId, '20' eqType FROM dual union all
SELECT '1500' plant, '003' fId, 'AAA-0003' eqId, '10' eqType FROM dual union all
SELECT '1500' plant, '004' fId, 'AAA-0004' eqId, '10' eqType FROM dual union all
SELECT '1500' plant, '005' fId, 'AAA-0005' eqId, '20' eqType FROM dual union all
SELECT '1500' plant, '006' fId, 'AAA-0006' eqId, '20' eqType FROM dual union all
SELECT '1500' plant, '007' fId, 'AAA-0007' eqId, '10' eqType FROM dual
)

WITH t06 AS
(
SELECT '1500' plant,'1' levelno , '1' seq , '10' unit , '' part , '' equip FROM DUAL UNION ALL
SELECT '1500' plant,'1' levelno , '2' seq , '20' unit , '' part , '' equip FROM DUAL UNION ALL
SELECT '1500' plant,'2' levelno , '1' seq , '10' unit , 'A' part , '' equip FROM DUAL UNION ALL
SELECT '1500' plant,'2' levelno , '2' seq , '20' unit , 'B' part , '' equip FROM DUAL UNION ALL
SELECT '1500' plant,'3' levelno , '1' seq , '10' unit , 'A' part , '001-1' equip FROM DUAL UNION ALL
SELECT '1500' plant,'3' levelno , '2' seq , '10' unit , 'A' part , '002-1' equip FROM DUAL 
)

 

테이블 3개를 조인해서 하나의 tree 구조로 만들려고 합니다.

자꾸 질문드려 죄송하네요..

t03.fcType
      ㄴ   t03.flType
           ㄴ  t03.fId || t03.flNm
               ㄴ t01.eqId
                  ㄴ t06.unit
                     ㄴ t06.part
                        ㄴ t06.equip

이런 계층 구조의 데이터를 뽑아 내려고 합니다.

조인 조건은 각 테이블의 같은 컬럼 값이 있습니다.

예제를 찾으려고 했는데 잘 안보이는것 같네요...

참고가 될진 모르겠지만.. to6 의 구조는 아래와 같이 표현합니다.

 

SELECT LEVELNO
     , SEQ
     , UNIT
     , PART
     , EQUIP
  FROM (
        SELECT LEVELNO
             , SEQ
             , UNIT
             , PART
             , EQUIP
             , CASE WHEN PART IS NULL THEN '1' ELSE PART
                    END PART_ORDER_BY
          FROM T
         ORDER BY SEQ , UNIT  , PART_ORDER_BY
         )

 

by 창조의날개 [2015.04.02 19:32:44]

지난번에 마농님 쿼리 컨닝해서 만들었는데..

맞는건지는 모르겠네요..

그리고 T06 테이블에 NULL  값들이 있어서 확인이 안되서 일단 임의값을 넣었습니다.

업무 내용을 몰라 어떻게 해야 할지??


WITH t03 AS
(
SELECT '1500' plant, '001' fId, '설비1' flNm, 'f10' fcType, '1' flType FROM dual union all
SELECT '1500' plant, '002' fId, '설비2' flNm, 'f10' fcType, '2' flType FROM dual union all
SELECT '1500' plant, '003' fId, '설비3' flNm, 'f20' fcType, '1' flType FROM dual union all
SELECT '1500' plant, '004' fId, '설비4' flNm, 'f20' fcType, '2' flType FROM dual union all
SELECT '1500' plant, '005' fId, '설비5' flNm, 'f30' fcType, '1' flType FROM dual
)
, t01 AS
(
SELECT '1500' plant, '001' fId, 'AAA-0001' eqId, '10' eqType FROM dual union all
SELECT '1500' plant, '002' fId, 'AAA-0002' eqId, '20' eqType FROM dual union all
SELECT '1500' plant, '003' fId, 'AAA-0003' eqId, '10' eqType FROM dual union all
SELECT '1500' plant, '004' fId, 'AAA-0004' eqId, '10' eqType FROM dual union all
SELECT '1500' plant, '005' fId, 'AAA-0005' eqId, '20' eqType FROM dual union all
SELECT '1500' plant, '006' fId, 'AAA-0006' eqId, '20' eqType FROM dual union all
SELECT '1500' plant, '007' fId, 'AAA-0007' eqId, '10' eqType FROM dual
)
, t06 AS
(
SELECT '1500' plant,'1' levelno , '1' seq , '10' unit , 'A1' part , '01' equip FROM DUAL UNION ALL
SELECT '1500' plant,'1' levelno , '2' seq , '20' unit , 'A2' part , '02' equip FROM DUAL UNION ALL
SELECT '1500' plant,'2' levelno , '1' seq , '10' unit , 'A' part , '03' equip FROM DUAL UNION ALL
SELECT '1500' plant,'2' levelno , '2' seq , '20' unit , 'B' part , '04' equip FROM DUAL UNION ALL
SELECT '1500' plant,'3' levelno , '1' seq , '10' unit , 'A' part , '001-1' equip FROM DUAL UNION ALL
SELECT '1500' plant,'3' levelno , '2' seq , '10' unit , 'A' part , '002-1' equip FROM DUAL 
)
SELECT DECODE(GROUPING_ID(t03.fcType, t03.flType, t03.fId, t01.eqId, t06.unit, t06.part, t06.equip)
     ,63,t03.fcType
     ,31,'      ㄴ   '||t03.flType
     ,15,'           ㄴ  '||t03.fId || t03.flNm
     ,7,'               ㄴ '||t01.eqId
     ,3,'                  ㄴ '||t06.unit
     ,1,'                     ㄴ '||t06.part
     ,0,'                        ㄴ '||t06.equip
     ) TREE
FROM T03, T01, T06
WHERE T03.PLANT = T01.PLANT
  AND T03.FID = T01.FID
  AND T03.PLANT = T06.PLANT
GROUP BY t03.fcType, ROLLUP(t03.flType, (t03.fId , t03.flNm), t01.eqId, t06.unit, t06.part, t06.equip)
ORDER BY t03.fcType
       , t03.flType NULLS FIRST
       , t03.fId NULLS FIRST
       , t01.eqId NULLS FIRST
       , t06.unit NULLS FIRST
       , t06.part NULLS FIRST
       , t06.equip NULLS FIRST
;

 


by 창조의날개 [2015.04.06 13:19:36]

SELECT DECODE(GROUPING_ID(t03.fcType, t03.flType, t03.fId, t01.eqId, t06.unit, t06.part, t06.equip)
     ,63,t03.fcType
     ,31,'      ㄴ   '||t03.flType
     ,15,'           ㄴ  '||t03.fId || t03.flNm
     ,7,'               ㄴ '||t01.eqId
     ,3,'                  ㄴ '||t06.unit
     ,1,'                     ㄴ '||t06.part
     ,0,'                        ㄴ '||t06.equip
     ) TREE
FROM T03, T01, T06
WHERE T03.PLANT = T01.PLANT
  AND T03.FID = T01.FID
  AND T03.PLANT = T06.PLANT
  AND T06.unit IS NOT NULL AND T06.part IS NOT NULL AND T06.equip IS NOT NULL
GROUP BY t03.fcType, ROLLUP(t03.flType, (t03.fId , t03.flNm), t01.eqId, t06.unit, t06.part, t06.equip)
ORDER BY t03.fcType
       , t03.flType NULLS FIRST
       , t03.fId NULLS FIRST
       , t01.eqId NULLS FIRST
       , t06.unit NULLS FIRST
       , t06.part NULLS FIRST
       , t06.equip NULLS FIRST
;

 


by 아발란체 [2015.04.03 10:16:17]

SQLP 문제 보다 어려운... 것 같은.... ㅠㅠ

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