꿈꾸는 개발자, DBA 커뮤니티 구루비

계층형쿼리 질문드립니다

by 김용한 [SQL Query] [2016.03.08 22:26:15]

계층형쿼리예제.xlsx (10,518Bytes)

	코드메인테이블 ( T )				코드서브테이블( T1 )				선택테이블( T2 )

	CODE	NAME	UPPERCODE	PRTNO	CODE	SUB_CODE	SUB_NAME	VALUE	CODE
	100000	코드1		0	111000	111000A	서브코드11-1	1	110000
선택값	110000	코드1-1	100000	1	111000	111000B	서브코드11-2	2	140000
	111000	코드1-1-1	110000	1	111000	111000C	서브코드11-3	5
	112000	코드1-1-2	110000	2	112000	112000A	서브코드12-1	7
	113000	코드1-1-3	110000	3	112000	112000B	서브코드12-2	15
	120000	코드1-2	100000	2	113000	113000A	서브코드13	18
	130000	코드1-3	100000	3	141000	141000A	서브코드41	22
	140000	코드1-4	100000	4	142000	142000A	서브코드42	43
	141000	코드1-4-1	140000	1
	142000	코드1-4-2	140000	2
	150000	코드1-5	100000	5
	160000	코드1-6	100000	6

	결과물


	CODE	NAME	UPPERCODE	전체값
	110000	코드1-1	100000	48
	140000	코드1-4	100000	65

자세한내용은 엑셀파일에 올립니다.

WITH T AS
(
select '100000' code, '코드1' name, '' uppercode, 0 prt_no from dual union all
select '110000' code, '코드1-1' name, '100000' uppercode, 1 prt_no from dual union all
select '111000' code, '코드1-1-1' name, '110000' uppercode, 1 prt_no from dual union all
select '112000' code, '코드1-1-2' name, '110000' uppercode, 2 prt_no from dual union all
select '113000' code, '코드1-1-3' name, '110000' uppercode, 3 prt_no from dual union all
select '120000' code, '코드1-2' name, '100000' uppercode, 2 prt_no from dual union all
select '130000' code, '코드1-3' name, '100000' uppercode, 3 prt_no from dual union all
select '140000' code, '코드1-4' name, '100000' uppercode, 4 prt_no from dual union all
select '141000' code, '코드1-4-1' name, '140000' uppercode, 1 prt_no from dual union all
select '142000' code, '코드1-4-2' name, '140000' uppercode, 2 prt_no from dual union all
select '150000' code, '코드1-5' name, '100000' uppercode, 5 prt_no from dual union all
select '160000' code, '코드1-6' name, '100000' uppercode, 6 prt_no from dual
), T1 AS (
SELECT '111000' CODE , '111000A' SUB_CODE , '서브코드11-1' SUB_NAME , '1' VALUE FROM DUAL UNION ALL
SELECT '111000' CODE , '111000B' SUB_CODE , '서브코드11-2' SUB_NAME , '2' VALUE FROM DUAL UNION ALL
SELECT '111000' CODE , '111000C' SUB_CODE , '서브코드11-3' SUB_NAME , '5' VALUE FROM DUAL UNION ALL
SELECT '112000' CODE , '112000A' SUB_CODE , '서브코드12-1' SUB_NAME , '7' VALUE FROM DUAL UNION ALL
SELECT '112000' CODE , '112000B' SUB_CODE , '서브코드12-2' SUB_NAME , '15' VALUE FROM DUAL UNION ALL
SELECT '113000' CODE , '113000A' SUB_CODE , '서브코드13' SUB_NAME , '18' VALUE FROM DUAL UNION ALL
SELECT '141000' CODE , '141000A' SUB_CODE , '서브코드41' SUB_NAME , '22' VALUE FROM DUAL UNION ALL
SELECT '142000' CODE , '142000A' SUB_CODE , '서브코드42' SUB_NAME , '43' VALUE FROM DUAL
), T2 AS (
SELECT '110000' CODE FROM DUAL UNION ALL
SELECT '140000' CODE FROM DUAL
)
SELECT T.*
, LEVEL AS LVL
, CONNECT_BY_ISLEAF AS LEAF
FROM T
START WITH T.UPPERCODE IS NULL
CONNECT BY
PRIOR T.CODE = T.UPPERCODE
ORDER
SIBLINGS BY
T.CODE
, T.PRT_NO

;

1.쿼리결과물인 LEAF = 1 인 T테이블의 CODE만 T1에 데이터가 존재한다는 가정입니다.

2.T2테이블은 T테이블을 계층형 쿼리로 전개했을때 선택한 최상위 CODE값들입니다

3. 결과물은 T2 개수만큼의 T1의 SUM을 보여주는것입니다...

by 창조의날개 [2016.03.09 09:56:44]

질문이 이해가 잘 안갑니다..

원하시는 결과도 알려주세요..

by 김용한 [2016.03.09 21:39:34]

나름 성의있게 질문올렸다고생각했는데 아닌가보군요 죄송합니다 ㅠㅠ

엑셀파일로 첨부파일내용에도 알차게(?) 질문올렸는데 못보셧나봐요 ^^

밑에 슈퍼히어로 마농님이 좋은 해결책을 주셨네요 ~~^^

by 마농 [2016.03.09 10:37:43]

SELECT a.code, a.name, a.uppercode
     , SUM(b.value) v
  FROM (SELECT CONNECT_BY_ROOT(code) code
             , CONNECT_BY_ROOT(name) name
             , CONNECT_BY_ROOT(uppercode) uppercode
             , code lowercode
          FROM t
         START WITH code IN (SELECT code FROM t2)
         CONNECT BY PRIOR code = uppercode
        ) a
     , t1 b
 WHERE a.lowercode = b.code
 GROUP BY a.code, a.name, a.uppercode
;

by 김용한 [2016.03.09 21:37:15]

무지어려웠는데 쉽게해결됐네요 감사드립니다 ~~^^

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변