오라클 퀴즈가 있길래 풀어봤다.

출처 : http://okjsp.pe.kr/seq/117567

 

문제 ------------

두번째 퀴즈입니다.

TABLE도 간단합니다.

퀴즈2> 아래 테이블을 잘 보고 윤봉길의 부모코드 전체와

자식코드 전체를 볼수있는 SQL 을 만들어 보세요.

TABLE LAYOUT

CODE        NAME       UP_CODE

1         홍길동 

2         박문수  1

3         심청     2

4         임꺽정  2

5         방정환  3

6         김유신  5

7         이태백  4

8         윤봉길  6

9         안창호  8

10       박태환  9

11       이효리  8

12       송승헌  11

13       전태일  8

14       박세리  13

 

 

 

 

내가 한 답 --------------------

 

테이블 생성
------------
CREATE TABLE TEST_SABJILI
(
CODE NUMBER,
NAME VARCHAR2(10 BYTE) ,
UP_CODE NUMBER
)
--------
insert
--------
SET DEFINE OFF;
Insert into TEST_SABJILI
(CODE, NAME)
Values
(1, '홍길동');
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(2, '박문수',1);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(3, '심청',2);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(4, '임꺽정',2);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(5, '방정환',3);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(6, '김유신',5);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(7, '이태백',4);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(8, '윤봉길',6);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(9, '안창호',8);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(10, '박태환',9);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(11, '이효리',8);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(12, '송승헌',11);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(13, '전태일',8);
Insert into TEST_SABJILI
(CODE, NAME,UP_CODE)
Values
(14, '박세리',13);

 

COMMIT;
-------------
조회
-------------
SELECT  lpad(' ', (level - 1) * 1, ' ') || NAME,CODE,UP_CODE,LEVEL FROM test_sabjili
WHERE CODE IN (
    SELECT code
        FROM test_sabjili 
        START WITH CODE = (SELECT CODE FROM test_sabjili WHERE NAME = '윤봉길' )
        CONNECT BY PRIOR UP_CODE = CODE
) OR CODE IN(
    SELECT code
        FROM test_sabjili 
        START WITH CODE = (SELECT CODE FROM test_sabjili WHERE NAME = '윤봉길' )
        CONNECT BY PRIOR CODE = UP_CODE
)
START WITH CODE = (
    SELECT CODE FROM (
        SELECT code, name, up_code ,ROWNUM AS RNUM
        FROM test_sabjili 
        START WITH CODE = (SELECT CODE FROM test_sabjili WHERE NAME = '윤봉길' )
        CONNECT BY PRIOR UP_CODE = CODE
        ORDER BY ROWNUM DESC
        ) WHERE ROWNUM = 1
)
CONNECT BY PRIOR CODE = UP_CODE
---------------------
결과
---------------------

홍길동  1  1
 박문수  2 1 2
  심청  3 2 3
   방정환 5 3 4
    김유신 6 5 5
     윤봉길 8 6 6
      안창호 9 8 7
       박태환 10 9 8
      이효리 11 8 7
       송승헌 12 11 8
      전태일 13 8 7
       박세리 14 13 8

 

그냥 결과만 짜맞췄다 흠좀무