출처 : 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
그냥 결과만 짜맞췄다 흠좀무