참◈서빈
2008-06-02 10:42:23
20
6293

퀴즈2> 조금 어려운 SQL퀴즈 - 부모코드 추적


두번째 퀴즈입니다.

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

0
0
  • 댓글 20

  • 쿠리우
    2008-06-02 11:56:02
    오라클인가요?
    0
  • nice05
    2008-06-02 13:28:07
    이거...숙제죠?
    0
  • 삽지리
    2008-06-02 16:28:16
    결과는 어떻게 디스플레이 되어야하나요?
    0
  • 참◈서빈
    2008-06-02 20:00:11
    db는 가리질 않지만 ORACLE이 좋겠지요....
    부모코드 목록, 자신것,자식코드목록이 차례로 나오면 되겠지요..
    해당사항 없는 거 제외하구요...
    0
  • 참◈서빈
    2008-06-02 20:00:52
    숙제 아닙니다.. ㅡㅡ;
    아~~!!!
    당첨되시면 이번엔 경품 드립니다.
    작고 요긴한 경품~ ㅎㅎ
    0
  • 삽지리
    2008-06-03 12:55:33
    usb일것 같은 예감.
    0
  • 참◈서빈
    2008-06-04 09:40:38
    문제가 너무 어려운가요? ㅡㅡ;;
    0
  • jisqo
    2008-06-04 10:55:49
    SELECT cd, LPAD (' ', NVL (upcd, 0) * 3) || NAME NAME, upcd
    FROM (SELECT *
    FROM (SELECT 1 cd, '홍길동' NAME, NULL upcd
    FROM DUAL
    UNION ALL
    SELECT 2 cd, '박문수' NAME, 1 upcd
    FROM DUAL
    UNION ALL
    SELECT 3 cd, '심청 ' NAME, 2 upcd
    FROM DUAL
    UNION ALL
    SELECT 4 cd, '임꺽정' NAME, 2 upcd
    FROM DUAL
    UNION ALL
    SELECT 5 cd, '방정환' NAME, 3 upcd
    FROM DUAL
    UNION ALL
    SELECT 6 cd, '김유신' NAME, 5 upcd
    FROM DUAL
    UNION ALL
    SELECT 7 cd, '이태백' NAME, 4 upcd
    FROM DUAL
    UNION ALL
    SELECT 8 cd, '윤봉길' NAME, 6 upcd
    FROM DUAL
    UNION ALL
    SELECT 9 cd, '안창호' NAME, 8 upcd
    FROM DUAL
    UNION ALL
    SELECT 10 cd, '박태환' NAME, 9 upcd
    FROM DUAL
    UNION ALL
    SELECT 11 cd, '이효리' NAME, 8 upcd
    FROM DUAL
    UNION ALL
    SELECT 12 cd, '송승헌' NAME, 11 upcd
    FROM DUAL
    UNION ALL
    SELECT 13 cd, '전태일' NAME, 8 upcd
    FROM DUAL
    UNION ALL
    SELECT 14 cd, '박세리' NAME, 13 upcd
    FROM DUAL)
    START WITH cd = 8
    CONNECT BY cd = PRIOR upcd
    UNION ALL
    SELECT *
    FROM (SELECT 1 cd, '홍길동' NAME, NULL upcd
    FROM DUAL
    UNION ALL
    SELECT 2 cd, '박문수' NAME, 1 upcd
    FROM DUAL
    UNION ALL
    SELECT 3 cd, '심청 ' NAME, 2 upcd
    FROM DUAL
    UNION ALL
    SELECT 4 cd, '임꺽정' NAME, 2 upcd
    FROM DUAL
    UNION ALL
    SELECT 5 cd, '방정환' NAME, 3 upcd
    FROM DUAL
    UNION ALL
    SELECT 6 cd, '김유신' NAME, 5 upcd
    FROM DUAL
    UNION ALL
    SELECT 7 cd, '이태백' NAME, 4 upcd
    FROM DUAL
    UNION ALL
    SELECT 8 cd, '윤봉길' NAME, 6 upcd
    FROM DUAL
    UNION ALL
    SELECT 9 cd, '안창호' NAME, 8 upcd
    FROM DUAL
    UNION ALL
    SELECT 10 cd, '박태환' NAME, 9 upcd
    FROM DUAL
    UNION ALL
    SELECT 11 cd, '이효리' NAME, 8 upcd
    FROM DUAL
    UNION ALL
    SELECT 12 cd, '송승헌' NAME, 11 upcd
    FROM DUAL
    UNION ALL
    SELECT 13 cd, '전태일' NAME, 8 upcd
    FROM DUAL
    UNION ALL
    SELECT 14 cd, '박세리' NAME, 13 upcd
    FROM DUAL)
    START WITH upcd = 8
    CONNECT BY PRIOR cd = upcd)
    ORDER BY cd ASC

    -- 레벨이 없어서 들여쓰기가 어색합니다 ㅎㅎㅎ;
    0
  • 참◈서빈
    2008-06-09 09:06:06
    너무 어려운건가요.... ㅡㅡ;;
    힌트를 살짝 드릴께요..

    union 과 connect by 를 써보세요.....
    많은 응모 바랍니다.
    0
  • 참◈서빈
    2008-06-09 09:22:28
    jisqo 님 잼있게 하셧네요... 피파 하다가 만나신 그분이죠?
    0
  • 삽지리
    2008-06-09 10:57:04
    샘플로 결과화면만 보여주시면 좀 좋을텐데..
    0
  • jisqo
    2008-06-09 11:06:04
    // 참◈서빈
    네 ㅋㅋㅋ 피파하다가 깜짝 놀랐습니다 참◈수빈 아이디가 익숙해서ㅎㅎ

    담에 같이 한겜 해요~
    0
  • 참◈서빈
    2008-06-09 13:42:18
    jisqo 님이 오라클 환경으로 간단하게 구현한것이 결과하면으로 참조할만 합니다.
    0
  • 후니
    2008-06-09 15:00:23
    저 구치소에 있을때 올라온 문재네요 ㅎㅎ
    0
  • graystones
    2008-06-09 17:54:33
    select * from (

    select code, name, up_code from raw_search_test
    where level > 1
    start with name='윤봉길'
    connect by prior up_code = code

    order by level desc )

    union all

    select code, name, up_code from raw_search_test

    start with name='윤봉길'
    connect by prior code = up_code
    0
  • 삽지리
    2008-06-10 15:47:46
    테이블 생성
    ------------
    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
    0
  • 삽지리
    2008-06-10 15:49:08
    결과
    -------------
    LPAD('*',(LEVEL-1)*1,'-')||NAME CODE UP_CODE LEVEL

    홍길동 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
    0
  • 참◈서빈
    2008-06-19 09:22:09
    많은(?) 분들의 답변 감사합니다.
    더이상 글이 올라오질 않네요...
    스스로 정답이라고 생각하시는 분 손들어주세요~~
    행운이 있을겁니다.
    0
  • graystones
    2008-06-25 16:45:34
    저요~! ^^;;;;;
    0
  • 참◈서빈
    2008-06-25 21:33:18
    graystones // jth8172@nate.com으로 메일 주세요...
    0
  • 로그인을 하시면 댓글을 등록할 수 있습니다.