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)
;
부탁 합니다.
행복한 하루 되세요.
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 ;