connect by 역전개와 자료 건수 얻기. 0 3 1,716

by 농부지기 [SQL Query] [2015.10.26 13:47:30]


WITH T1 AS
   ( SELECT ''       AS UP_PK1, '아시아' AS PK1, 1 AS O_SEQ FROM DUAL UNION ALL
     SELECT '아시아' AS UP_PK1, '한국'   AS PK1, 3 AS O_SEQ FROM DUAL UNION ALL
     SELECT '아시아' AS UP_PK1, '중국'   AS PK1, 4 AS O_SEQ FROM DUAL UNION ALL
     SELECT '아시아' AS UP_PK1, '필리핀' AS PK1, 2 AS O_SEQ FROM DUAL UNION ALL
     SELECT '아시아' AS UP_PK1, '홍공'   AS PK1, 1 AS O_SEQ FROM DUAL UNION ALL
     SELECT '아시아' AS UP_PK1, '호주'   AS PK1, 1 AS O_SEQ FROM DUAL UNION ALL
     
    
     SELECT       '' AS UP_PK1, '유럽'   AS PK1, 2 AS O_SEQ FROM DUAL UNION ALL
     SELECT '유럽'   AS UP_PK1, '프랑스' AS PK1, 3 AS O_SEQ FROM DUAL UNION ALL
     SELECT '유럽'   AS UP_PK1, '영국'   AS PK1, 1 AS O_SEQ FROM DUAL UNION ALL 
     SELECT '호주'   AS UP_PK1, '캔버라' AS PK1, 3 AS O_SEQ FROM DUAL
   )
  , T2 AS
   ( SELECT '한국'   AS R_PK1, 'A1' AS DT1 FROM DUAL UNION ALL
     SELECT '한국'   AS R_PK1, 'A2' AS DT1 FROM DUAL UNION ALL
     SELECT '제주도' AS R_PK1, 'A3' AS DT1 FROM DUAL UNION ALL
     SELECT '중국'   AS R_PK1, 'B1' AS DT1 FROM DUAL UNION ALL
     SELECT '중국'   AS R_PK1, 'B2' AS DT1 FROM DUAL UNION ALL
     SELECT '중국'   AS R_PK1, 'B3' AS DT1 FROM DUAL UNION ALL
     SELECT '프랑스' AS R_PK1, 'C1' AS DT1 FROM DUAL UNION ALL
     SELECT '영국'   AS R_PK1, 'D1' AS DT1 FROM DUAL UNION ALL
     SELECT '영국'   AS R_PK1, 'D2' AS DT1 FROM DUAL
   )
      
SELECT *
  FROM T1
 START WITH DT1  IN ('A1', 'A2', 'A3', 'B1', 'B2', 'B3', 'C1', 'D1', 'D2')

 CONNECT BY PK1 = PRIOR UP_PK1

  ;

 

최종결과 내용
1. 대륙별 자료 건수 조회
2. 국가별 자료 건수 조회
3. 역전개 후 정렬이 되어야 됨


원하는결과
------------
아시아(5)
   한국(2)
   중국(3)
   호주(1)
       캔버라(1)
유럽(3)
   프랑스(1)
   영국(2)

;

 

부탁 합니다.

행복한 하루 되세요.

 

by Jason [2015.10.26 14:28:37]
SELECT GROUPING(R_PK1) AS GRP
     , T1.UP_PK1
     , T2.R_PK1
     , COUNT(DT1) AS CNT
  FROM T2 T2, T1 T1
 WHERE T2.R_PK1 = T1.PK1
 GROUP BY T1.UP_PK1, ROLLUP(T2.R_PK1)
 ORDER BY UP_PK1, GROUPING(R_PK1) DESC, R_PK1 DESC

 


by 농부지기 [2015.10.26 14:34:33]

앗.. 이런방법도 있군요.

답변은 감사 한데요.

사실, 위에 있는 DATA는 단순 예제 이구요.

국가테이블로(T1) 표현한 자료는 레벨이 2단계 이상이라서 CONNECT BY 로 해결 해야 됩니다.

CONNECT BY 로 해결할 수있는 방법은 없을까요?


by 마농 [2015.10.26 20:58:26]
WITH T1 AS
(
SELECT '' up_pk1, '아시아' pk1, 1 o_seq FROM dual
UNION ALL SELECT '아시아', '한국'  , 3 FROM dual
UNION ALL SELECT '아시아', '중국'  , 4 FROM dual
UNION ALL SELECT '아시아', '필리핀', 2 FROM dual
UNION ALL SELECT '아시아', '홍공'  , 1 FROM dual
UNION ALL SELECT '아시아', '호주'  , 1 FROM dual
UNION ALL SELECT ''      , '유럽'  , 2 FROM dual
UNION ALL SELECT '유럽'  , '프랑스', 3 FROM dual
UNION ALL SELECT '유럽'  , '영국'  , 1 FROM dual
UNION ALL SELECT '호주'  , '캔버라', 3 FROM dual
)
, T2 AS
( 
SELECT '한국' r_pk1, 'A1' dt1 FROM dual
UNION ALL SELECT '한국'  , 'A2' FROM dual
UNION ALL SELECT '제주도', 'A3' FROM dual
UNION ALL SELECT '중국'  , 'B1' FROM dual
UNION ALL SELECT '중국'  , 'B2' FROM dual
UNION ALL SELECT '중국'  , 'B3' FROM dual
UNION ALL SELECT '프랑스', 'C1' FROM dual
UNION ALL SELECT '영국'  , 'D1' FROM dual
UNION ALL SELECT '영국'  , 'D2' FROM dual
)
SELECT LPAD(' ', (lv-1)*2, ' ') || a.pk1 pk1
     , COUNT(*) cnt
  FROM (-- 1. 정렬용 계층 쿼리
        SELECT pk1
             , LEVEL lv
             , ROWNUM rn
          FROM t1
         START WITH up_pk1 IS NULL
         CONNECT BY PRIOR pk1 = up_pk1
         ORDER SIBLINGS BY o_seq
        ) a
     , (-- 2. 집계용 계층 쿼리
        SELECT CONNECT_BY_ROOT(pk1) pk1
             , pk1 r_pk1
          FROM t1
         CONNECT BY PRIOR pk1 = up_pk1
        ) b
     , t2 c
 WHERE a.pk1 = b.pk1
   AND b.r_pk1 = c.r_pk1
 GROUP BY a.pk1, a.lv, a.rn
 ORDER BY rn
;

 

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