오라클 쿼리 질문 0 2 4,125

by 트집이 [SQL Query] oracle query [2018.01.31 15:47:55]


with org AS (
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1234' as code, 'RMI1' as code_name from dual union all
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1235' as code, 'RMI2' as code_name from dual union all
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1236' as code, 'RMI3' as code_name from dual union all
    select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB1111' as code, 'RMI1' as code_name from dual union all
    select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB2222' as code, 'RMI2' as code_name from dual
) select * from org;

위와 같은 org 테이블이 있다고 가정합니다.

pcode 컬럼의 데이터는 무조건 2레벨 데이터이고 code 컬럼이 3레벨 데이터 입니다.

2레벨의 상위(pcode)코드는 항상 'S100'이고 3레벨의 상위(pcode)는 같은 행의 pcode를 따라가야 합니다.

그리고 ord 값은 order by lvl, pcode, code 순으로 정렬해서 순서값을 매겨주면 됩니다.

이럴경우 아래와 같이 쿼리를 하였는데... 좀 더 깔끔한(?) 방법이 없을까요?

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

with org AS (
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1234' as code, 'RMI1' as code_name from dual union all
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1235' as code, 'RMI2' as code_name from dual union all
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1236' as code, 'RMI3' as code_name from dual union all
    select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB1111' as code, 'RMI1' as code_name from dual union all
    select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB2222' as code, 'RMI2' as code_name from dual
)
select 
        lvl
      , code
      , code_name
      , pcode
      , rownum as ord
from 
(      
        select 
                lvl
              , code
              , code_name
              , pcode
        from 
        (
                select 2 as lvl
                      , pcode as code
                      , pcode_name as code_name
                      , 'S100' as pcode
                from (      
                    select distinct pcode, pcode_name
                    from org
                )
                UNION ALL
                select 3 as lvl
                      , code as code
                      , code_name as code_name
                      , pcode as pcode
                from (      
                    select distinct pcode, pcode_name, code, code_name
                    from org
                )
        )
        order by 
                lvl
              , pcode
              , code
)   

by 우리집아찌 [2018.01.31 16:12:38]
-- 그냥.. 똑같이만..
with org AS (
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1234' as code, 'RMI1' as code_name from dual union all
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1235' as code, 'RMI2' as code_name from dual union all
    select 'D022' as pcode, 'A대리점' AS pcode_name, 'AP1236' as code, 'RMI3' as code_name from dual union all
    select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB1111' as code, 'RMI1' as code_name from dual union all
    select 'D035' as pcode, 'B대리점' AS pcode_name, 'AB2222' as code, 'RMI2' as code_name from dual
) 

SELECT LV , CODE , CODE_NAME , PCODE 
     , ROW_NUMBER() OVER(ORDER BY LV , PCODE , CODE ) RN
  FROM (SELECT DISTINCT  2 AS LV , PCODE CODE , PCODE_NAME AS CODE_NAME, 'S100' AS PCODE  FROM ORG
         UNION ALL
        SELECT  3 AS LV ,  CODE  , CODE_NAME  , PCODE  FROM ORG ) A
 ORDER BY LV , PCODE , CODE

 


by 마농 [2018.01.31 17:45:52]
WITH org AS
(
SELECT 'D022' pcode, 'A대리점' pcode_name, 'AP1234' code, 'RMI1' code_name FROM dual
UNION ALL SELECT 'D022', 'A대리점', 'AP1235', 'RMI2' FROM dual 
UNION ALL SELECT 'D022', 'A대리점', 'AP1236', 'RMI3' FROM dual 
UNION ALL SELECT 'D035', 'B대리점', 'AB1111', 'RMI1' FROM dual 
UNION ALL SELECT 'D035', 'B대리점', 'AB2222', 'RMI2' FROM dual
)
-- UNION --
SELECT lvl
     , code
     , code_name
     , pcode
     , ROWNUM ord
  FROM (SELECT 3 lvl, pcode, code, code_name FROM org
         UNION  -- 유니온을 이용한 중복 제거 및 정렬 --
        SELECT 2, 'S100', pcode, pcode_name FROM org
        )
;
-- ROLLUP --
SELECT lvl
     , code
     , code_name
     , pcode
     , ROWNUM ord
  FROM (SELECT NVL2(code, 3, 2) lvl
             , NVL(code, pcode) code
             , NVL(code_name, pcode_name) code_name
             , NVL2(code, pcode, 'S100') pcode
          FROM org
         GROUP BY pcode, pcode_name, ROLLUP((code, code_name))
         ORDER BY lvl, pcode, code
        )
;
-- PIVOT --
SELECT lvl
     , code
     , code_name
     , pcode
     , ROWNUM ord
  FROM (SELECT DISTINCT *
          FROM (SELECT 'S100' ppcode
                     , pcode, pcode_name, code, code_name
                  FROM org
                )
         UNPIVOT ((code, code_name, pcode) FOR lvl IN ( ( code,  code_name,  pcode) AS 3
                                                      , (pcode, pcode_name, ppcode) AS 2
                                                      ) )
         ORDER BY lvl, pcode, code
        )
;
-- 복제 --
SELECT lvl
     , code
     , code_name
     , pcode
     , ROWNUM ord
  FROM (SELECT DISTINCT
               lvl
             , DECODE(lvl, 2, pcode, code) code
             , DECODE(lvl, 2, pcode_name, code_name) code_name
             , DECODE(lvl, 2, 'S100', pcode) pcode
          FROM org
             , (SELECT LEVEL + 1 lvl FROM dual CONNECT BY LEVEL <= 2)
         ORDER BY lvl, code, pcode
        )
;

 

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