(Oracle Tip) 계층구조 테이블의 Query _탑크리에듀

계층 구조의 테이블을 SELECT하는 방법은 일반 테이블의 SELECT 방법과 조금 다르게 이루어 집니다. 계층 구조의 모델링을 Recusive Referential(회귀적 참조)라고도 합니다. 그럼 Table을 만들고 데이터를 넣은 다음에 SELECT 해 보겠습니다. 데이터는 11번가의 분류를 참조 했습니다. (절대 PR하기 위한 건 아닙니다.ㅋㅋㅋ) -- 테이블 생성 CREATE TABLE CATEGORY ( CATEGORY_ID NUMBER(6) NOT NULL, CATEGORY_NM VARCHAR2(60 BYTE), PAR_CATEGORY_ID NUMBER(6) ); -- PK ALTER TABLE CATEGORY ADD ( CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORY_ID) USING INDEX); -- FK ALTER TABLE CATEGORY ADD CONSTRAINTS FK_CATEGORY FOREIGN KEY (PAR_CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID); -- DATA INSERT INSERT INTO CATEGORY VALUES ('100001','카테고리최상위',''); INSERT INTO CATEGORY VALUES ('100002','브랜드패션','100001'); INSERT INTO CATEGORY VALUES ('100003','의류','100001'); INSERT INTO CATEGORY VALUES ('100004','잡화/뷰티','100001'); INSERT INTO CATEGORY VALUES ('100005','식품/유아동','100001'); INSERT INTO CATEGORY VALUES ('100006','리빙/건강','100001'); INSERT INTO CATEGORY VALUES ('100007','레저/자동차','100001'); INSERT INTO CATEGORY VALUES ('100008','디지털/가전','100001'); INSERT INTO CATEGORY VALUES ('100009','도서/잡화','100001'); INSERT INTO CATEGORY VALUES ('100010','브랜드 여성의류/언더웨어','100002'); INSERT INTO CATEGORY VALUES ('100011','브랜드 남성의류/언더웨어','100002'); INSERT INTO CATEGORY VALUES ('100012','브랜드 캐주얼의류','100002'); INSERT INTO CATEGORY VALUES ('100013','TV/냉장고/세탁기','100008'); INSERT INTO CATEGORY VALUES ('100014','주방/이미용/생활가전','100008'); INSERT INTO CATEGORY VALUES ('100015','전기매트/가습기/난방가전','100008'); INSERT INTO CATEGORY VALUES ('100016','DSLR/디카/액세서리','100008'); INSERT INTO CATEGORY VALUES ('100017','MP3/AV/음향가전','100008'); INSERT INTO CATEGORY VALUES ('100018','노트북/데스크탑','100008'); INSERT INTO CATEGORY VALUES ('100019','모니터/프린터/잉크','100008'); INSERT INTO CATEGORY VALUES ('100020','PC부품/주변기기','100008'); INSERT INTO CATEGORY VALUES ('100021','저장장치/스마트패드/게임','100008'); INSERT INTO CATEGORY VALUES ('100022','휴대폰/액세서리','100008'); INSERT INTO CATEGORY VALUES ('100023','LED TV','100013'); INSERT INTO CATEGORY VALUES ('100024','3D TV','100013'); INSERT INTO CATEGORY VALUES ('100025','LCD TV','100013'); INSERT INTO CATEGORY VALUES ('100026','PDP TV','100013'); INSERT INTO CATEGORY VALUES ('100027','브라운관 TV','100013'); INSERT INTO CATEGORY VALUES ('100028','TV 주변기기','100013'); INSERT INTO CATEGORY VALUES ('100029','양문형냉장고','100013'); INSERT INTO CATEGORY VALUES ('100030','일반형냉장고','100013'); INSERT INTO CATEGORY VALUES ('100031','김치냉장고','100013'); INSERT INTO CATEGORY VALUES ('100032','기능성냉장고','100013'); INSERT INTO CATEGORY VALUES ('100033','드럼세탁기','100013'); INSERT INTO CATEGORY VALUES ('100035','삼성전자 매장','100013'); INSERT INTO CATEGORY VALUES ('100036','리퍼/반품/전시','100013'); INSERT INTO CATEGORY VALUES ('100037','렌탈서비스','100013'); INSERT INTO CATEGORY VALUES ('100038','삼성전자','100023'); INSERT INTO CATEGORY VALUES ('100039','LG전자','100023'); INSERT INTO CATEGORY VALUES ('100040','브랜드기타','100023'); INSERT INTO CATEGORY VALUES ('100041','삼성전자','100024'); INSERT INTO CATEGORY VALUES ('100042','LG전자','100024'); INSERT INTO CATEGORY VALUES ('100043','소니','100024'); INSERT INTO CATEGORY VALUES ('100044','노트북','100018'); INSERT INTO CATEGORY VALUES ('100045','브랜드PC','100018'); INSERT INTO CATEGORY VALUES ('100046','조립PC본체','100018'); INSERT INTO CATEGORY VALUES ('100047','조립PC세트','100018'); INSERT INTO CATEGORY VALUES ('100048','태블릿PC','100018'); INSERT INTO CATEGORY VALUES ('100049','중고/리퍼/반품/전시','100018'); INSERT INTO CATEGORY VALUES ('100050','렌탈서비스','100018'); INSERT INTO CATEGORY VALUES ('100051','노트북액세서리','100018'); INSERT INTO CATEGORY VALUES ('100052','삼성전자','100044'); INSERT INTO CATEGORY VALUES ('100053','애플','100044'); INSERT INTO CATEGORY VALUES ('100054','한성','100044'); INSERT INTO CATEGORY VALUES ('100055','코어i7','100052'); INSERT INTO CATEGORY VALUES ('100056','코어i5','100052'); INSERT INTO CATEGORY VALUES ('100057','코어i3','100052'); INSERT INTO CATEGORY VALUES ('100058','코어2듀오','100052'); INSERT INTO CATEGORY VALUES ('100059','AMD','100052'); INSERT INTO CATEGORY VALUES ('100060','i7','100054'); INSERT INTO CATEGORY VALUES ('100061','i5','100054'); INSERT INTO CATEGORY VALUES ('100062','펜티엄 이하','100054'); INSERT INTO CATEGORY VALUES ('100063','CPU','100020'); INSERT INTO CATEGORY VALUES ('100064','메인보드','100020'); INSERT INTO CATEGORY VALUES ('100065','데스크탑용 메모리(RAM)','100020'); INSERT INTO CATEGORY VALUES ('100066','노트북용 메모리(RAM)','100020'); INSERT INTO CATEGORY VALUES ('100067','기타PC부품','100020'); INSERT INTO CATEGORY VALUES ('100068','코어i7','100063'); INSERT INTO CATEGORY VALUES ('100069','코어i5','100063'); INSERT INTO CATEGORY VALUES ('100070','코어i3','100063'); INSERT INTO CATEGORY VALUES ('100071','코어i3/i5/i7','100064'); INSERT INTO CATEGORY VALUES ('100072','코어2듀오이상','100064'); INSERT INTO CATEGORY VALUES ('100073','스포츠의류/운동화/용품','100007'); INSERT INTO CATEGORY VALUES ('100074','등산/아웃도어/캠핑/낚시','100007'); INSERT INTO CATEGORY VALUES ('100075','바다낚시장비','100074'); INSERT INTO CATEGORY VALUES ('100076','낚시대/뜰채','100075'); INSERT INTO CATEGORY VALUES ('100077','바다 낚시 세트','100075'); INSERT INTO CATEGORY VALUES ('100078','바늘류','100075'); INSERT INTO CATEGORY VALUES ('100079','갯바위릴대','100076'); INSERT INTO CATEGORY VALUES ('100080','바다루어대','100076'); INSERT INTO CATEGORY VALUES ('100081','우럭/지깅 선상대','100076'); SELECT * FROM CATEGORY; -- 이제 계층 구조 SELECT 입니다. SELECT LEVEL,LPAD(' ', 2*LEVEL-1)||CATEGORY_ID||' '||CATEGORY_NM FROM CATEGORY A START WITH PAR_CATEGORY_ID IS NULL CONNECT BY PRIOR CATEGORY_ID = PAR_CATEGORY_ID ORDER BY LEVEL, CATEGORY_ID; 위 는 방향성이 위에서 아래 쪽입니다. 반대로 아래에서 위 쪽으로 찾아 갑니다. SELECT LEVEL,LPAD(' ', 2*LEVEL-1)||CATEGORY_ID||' '||CATEGORY_NM FROM CATEGORY A START WITH CATEGORY_ID = 100055 CONNECT BY PRIOR PAR_CATEGORY_ID = CATEGORY_ID; START WITH 문과 COONECT BY 문을 첫번째 QUERY문과 비교해 보십시요. 데이터의 갯수가 많으면 자신의 부모를 찾기 힘듦니다. 그럴때 자신의 부모가 보이면 좋겠지요. 그런 함수가 있습니다. SELECT LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(CATEGORY_NM,'-') PATH FROM CATEGORY A START WITH PAR_CATEGORY_ID IS NULL CONNECT BY PRIOR CATEGORY_ID = PAR_CATEGORY_ID ORDER BY LEVEL, CATEGORY_ID; 결과를 보시면 아실겁니다. 그럼 참고하시고... 감사합니다.