{"id":407,"date":"2015-08-24T04:50:20","date_gmt":"2015-08-24T04:50:20","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=407"},"modified":"2015-08-24T04:50:20","modified_gmt":"2015-08-24T04:50:20","slug":"sql-%ec%9e%91%ec%84%b1%ec%8b%9c-%ea%b3%84%ec%b8%b5%ed%98%95-%eb%8d%b0%ec%9d%b4%ed%84%b0-%ed%91%9c%ed%98%84","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=407","title":{"rendered":"SQL \uc791\uc131\uc2dc \uacc4\uce35\ud615 \ub370\uc774\ud130 \ud45c\ud604"},"content":{"rendered":"

\uacc4\uce35\ud615 \ub370\uc774\ud130 \ud45c\ud604\uc774\ub780 ?<\/p>\n

<\/p>\n

\uac04\ub2e8\ud558\uac8c \uc124\uba85\ud558\uc790\uba74 \uc0c1\uc704 \uad6c\uc870\uac00 \uc788\uace0 \ud558\uc704\uad6c\uc870\uac00 \uc874\uc7ac\ud560\ub54c\uc5d0 \uadf8 \uad6c\uc870\ub97c \ud45c\ud604\ud558\ub294\uac83<\/p>\n

<\/p>\n

\uc774\uac8c \ub354 \uc5b4\ub824\uc6b8\uc218 \uc788\uc73c\ub2c8 \uc608\ub97c \ub4e4\uc790\uba74<\/p>\n

<\/p>\n

\ud68c\uc0ac\ub85c \ub530\uc9c0\uba74 \uc0ac\uc7a5\uc774 \uc874\uc7ac\ud558\uace0 \ubd80\uc0ac\uc7a5\uc774 \uc874\uc7ac\ud558\uace0 \uac01 \ubd80\uc11c\uc758 \uc9f1\ub4e4\uc774 \uc874\uc7ac\ud560\ud150\ub370 \uadf8\uac78 \ub098\ud0c0\ub0b4\ubcf4\uba74<\/p>\n

<\/p>\n

\uc0ac\uc7a5<\/p>\n

      \ubd80\uc0ac\uc7a5<\/p>\n

                  \ucd1d\ubb34\ubd80\uc9f1<\/p>\n

                                \ucd1d\ubb34\ubd80\ucc28\uc7a5<\/p>\n

                                                 \ucd1d\ubb34\ubd80\uacfc\uc7a5<\/p>\n

                                                                  \ucd1d\ubb34\ubd80\ub300\ub9ac<\/p>\n

                                                                                   \uc0ac\uc6d01<\/p>\n

                                                                                    \uc0ac\uc6d02 <\/p>\n

\ubb50 \uc774\ub7f0\uc2dd\uc73c\ub85c \ucffc\ub9ac \uacb0\uacfc\ub97c \ud45c\ud604\ud558\ub294\uac83\uc744 \uacc4\uce35\ud615 \ub370\uc774\ud130 \ud45c\ud604\uc774\ub77c\uace0 \ub9d0\ud560\uc218 \uc788\ub2e4.<\/p>\n

<\/p>\n

\uc6b0\ub9ac\uac00 \uc0ac\uc6a9\ud558\ub294 \uac04\ub2e8\ud55c emp table\uc744 \uc870\uc0ac\ud574 \ubcf4\uba74 . . .<\/p>\n

<\/p>\n

empno \uceec\ub7fc\uc774 PK\ub85c \uc124\uc815\uc774 \ub418\uc5b4\uc788\uace0 mgr \uceec\ub7fc\uc740 FK\uceec\ub7fc\uc73c\ub85c \uc124\uc815\uc774 \ub418\uba74\uc11c empno\uceec\ub7fc\uc758 \uac12\uc744 \ucc38\uc870\ud558\uac8c \ub41c\ub2e4.<\/p>\n

<\/p>\n

<\/p>\n

\uc774\ub7f0 \uacc4\uce35\ud615 \uad6c\uc870\ub97c \ud45c\ud604\ud558\uace0\uc790 \ud560\ub54c \uc0ac\uc6a9\ud558\ub294\uac83\uc774 level \uc774\ub77c\ub294 \uc758\uc0ac\uc5f4\uc744 \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n

\uacc4\uce35\uc774 \ud558\ub098\uc529 \ub4e4\uc5b4\uac08\ub54c\ub9c8\ub2e4 level \uac12\uc774 0 \ubd80\ud130 \ud558\ub098\uc529 \uc99d\uac00\ub97c \ud55c\ub2e4.<\/p>\n

<\/p>\n

<\/p>\n

\uadf8\ub7fc \uc774\ub7f0 \uacc4\uce35\uad6c\uc870\ub97c \ub098\ud0c0\ub0b4\uae30 \uc704\ud574\uc11c \uc5b4\ub5bb\uac8c \ud574\uc57c \ud558\ub098 ?<\/p>\n

<\/p>\n

<\/p>\n

Connect by [PRIOR]  \uceec\ub7fc1 = [PRIOR]  \uceec\ub7fc2 <\/p>\n

START WITH  \uc870\uac74 <\/p>\n

<\/p>\n

\uc774 \ubd80\ubd84\uc744 \ucd94\uac00\ud55c\ub2e4 . <\/p>\n

<\/p>\n

<\/p>\n

<\/p>\n

PRIOR\ub77c\ub294 \ud0a4\uc6cc\ub4dc\uac00 \uceec\ub7fc 1 \ucabd\uc5d0 \ubd99\uc73c\uba74 \uc804\uc704\ud615( root – left – right ) \ud615\ud0dc\uac00 \ub41c\ub2e4.<\/p>\n

\uc989 PRIOR\ub97c \uc67c\ucabd\uc5d0 \ubd99\uc774\uba74\uc11c \uceec\ub7fc1 \uac12\uc5d0 PK\uceec\ub7fc\uac12\uc744 \uc8fc\uba74\ub41c\ub2e4 .(\ubd80\ubaa8\uc5d0\uc11c \uc790\uc2dd\uc73c\ub85c \uac80\uc0c9)<\/p>\n

PRIOR\ub77c\ub294 \ud0a4\uc6cc\ub4dc\uac00 \uceec\ub7fc2 \ucabd\uc5d0 \ubd99\uc73c\uba74 \ud6c4\uc704\ud615( left – right – root )\ud615\ub300\uac00 \ub41c\ub2e4.<\/p>\n

\uc774\ub54c\ub294 \uc790\uc2dd\uc5d0\uc11c \ubd80\ubaa8\ub85c \uac12\uc774 \ucd9c\ub825\ub418\ub294 \ud615\ud0dc\uac00 \ub418\ub294\uac83\uc774\ub2e4.<\/p>\n

\uadf8 \ub2e4\uc74c START WITH \uc870\uac74 \ubd80\ubd84\uc740 LEVEL\uc744 \uc5b4\ub514\ubd80\ud130 \uc801\uc6a9\ud560\uac83\uc778\uc9c0\ub97c \ub098\ud0c0\ub0b4\uac8c \ub41c\ub2e4.<\/p>\n

<\/p>\n

<\/p>\n

\uac04\ub2e8\ud558\uac8c \uc608\ub97c \ub4e4\uc5b4\ubcf4\uba74<\/p>\n

<\/p>\n

SELECT ( ‘  ‘ , LEVEL * 5 ) || ename<\/p>\n

FROM     emp<\/p>\n

CONNECT  BY   PRIOR  empno = mgr<\/p>\n

START  WITH mgr  IS NULL ;<\/p>\n

<\/p>\n

\uc704\uc5d0\uaec4 \ud480\uc5b4\ubcf4\uc790\uba74 empno \uc640 mgr\uc774 \uac19\uc740\uac83\uc744 \ucc3e\uc544\uc11c \ubfcc\ub824\uc8fc\ub418 \uc2dc\uc791 \uc810\uc740 mgr \uc774 NULL\uc774 \ub418\ub294\uac83\ubd80\ud130 LEVEL\uc744 \uc801\uc6a9\uc2dc\ud0a4\ub77c \ud558\ub294\ub73b\uc774\ub2e4 . <\/p>\n

<\/p>\n

\uc989 mgr \uc774 NULL\uc774 \ub418\ub294\uac83\uc774 Root\uac00 \ub418\ub294\uac83\uc774\uace0  ( ‘  ‘ , LEVEL * 5 ) || ename \uc774\ub77c\ub294\uac83\uc740 \ub808\ubca8\uc5d0 \ub9de\uac8c\ub054 \uc55e\uc5d0 \uacf5\ubc31\uc744 \ub2e4\uc12f\uac1c\uc529 \ubd99\uc778\ud6c4\uc5d0 \uc774\ub984\uc744 \ucd9c\ub825\ud558\uc790\ub294\uac83\uc774\ub2e4.<\/p>\n

<\/p>\n

<\/p>\n

=========   \uacb0\uacfc\ub97c \ubcf4\uba74  ============<\/p>\n

<\/p>\n

RPAD(”,LEVEL*5)||ENAME<\/p>\n

——————————————————————————–<\/p>\n

     KING<\/p>\n

              JONES<\/p>\n

                        SCOTT<\/p>\n

                                    ADAMS<\/p>\n

                         FORD<\/p>\n

                                    SMITH<\/p>\n

               BLAKE<\/p>\n

                         ALLEN<\/p>\n

                         WARD<\/p>\n

                         MARTIN<\/p>\n

                         TURNER<\/p>\n

                         JAMES<\/p>\n

                CLARK<\/p>\n

                         MILLER<\/p>\n

<\/p>\n

14 \uac1c\uc758 \ud589\uc774 \uc120\ud0dd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n

<\/p>\n

\uc694\ub7f0\uc2dd\uc758 \uacb0\uacfc\uac00 \ub098\uc628\ub2e4 .<\/p>\n

<\/p>\n

<\/p>\n

\uadf8\ub7fc PRIOR\ub97c \uc624\ub978\ucabd\uc5d0 \ubd99\uc5ec \ubcf8\ub2e4\uba74 . . .<\/p>\n

<\/p>\n

SELECT   RPAD( ‘ ‘, LEVEL * 5 ) || ename<\/p>\n

FROM      emp<\/p>\n

CONNECT BY  empno =  PRIOR mgr<\/p>\n

START WITH ename =  ‘ADAMS’ ;<\/p>\n

<\/p>\n

\uc989 \uc2dc\uc791\uc704\uce58\uac00 \uc790\uc2dd\ubd80\ud130 \uac80\uc0c9\ud574 \ub098\uac00\ub294\uac83\uc774\ub2c8\uae50 . . .<\/p>\n

\uc544\ub2f4\uc2a4\ub77c\ub294 \uc824 \ud558\uc704 \ub808\ubca8\ubd80\ud130 \uc8fc\uba74 \uadf8 \ub808\ubca8\uc5d0 \ud574\ub2f9\ud558\ub294 \uc0c1\uc758\uac12\uc744 \uc5bb\uc744\uc218 \uc788\ub2e4.<\/p>\n

<\/p>\n

============  \uacb0\uacfc\ub97c \ubcf4\uba74 =============<\/p>\n

<\/p>\n

RPAD(”,LEVEL*5)||ENAME<\/p>\n

————————————-<\/p>\n

     ADAMS<\/p>\n

                  SCOTT<\/p>\n

                              JONES<\/p>\n

                                          KING<\/p>\n

<\/p>\n

\uc774\ub7f0\uc2dd\uc758 \uacb0\uacfc\ub97c \uc5bb\uc744\uc218\uac00 \uc788\uac8c \ub41c\ub2e4 .<\/p>\n

<\/p>\n

– \uc751\uc6a9<\/p>\n

<\/p>\n

SCOTT\uac00 \uad00\ub9ac\ud558\ub294 \uc0ac\uc6d0\uc744 \ud2b8\ub9ac \uad6c\uc870\ub85c \ubcf4\uace0\uc790 \ud55c\ub2e4\uba74 \uc5b4\ub5bb\uac8c \ud558\uba74 \ub420\uae4c ?<\/p>\n

<\/p>\n

\uc704\uc5d0\uc11c \uc544\ub798\ub85c \uac80\uc0c9\ud558\ub294\uac83\uc774\ubbc0\ub85c . . .<\/p>\n

PRIOR\uac00 \uc67c\ucabd \uc989 PK\uceec\ub7fc\uc5d0 \ubd99\uc73c\uba74 \ub418\ub294\uac83\uc774\uace0 \uc824 \uc704 \ub808\ubca8\uc744 SCOTT\ub85c \uc8fc\uba74 \ub418\uaca0\uc838 ?<\/p>\n

<\/p>\n

SELECT  RPAD( ‘ ‘, LEVEL * 5 ) || ename<\/p>\n

FROM      emp<\/p>\n

CONNECT BY  PRIOR empno =   mgr<\/p>\n

START WITH ename =  ‘SCOTT’ ;<\/p>\n

<\/p>\n

======== \uacb0\uacfc =========<\/p>\n

<\/p>\n

RPAD(”,LEVEL*5)||ENAME<\/p>\n

——————————–<\/p>\n

     SCOTT<\/p>\n

                  ADAMS<\/p>\n

<\/p>\n

<\/p>\n

<\/p>\n

\uc790 \uadf8\ub7fc \uac01 \ub808\ubca8\ubcc4\ub85c \uc0ac\ub78c\uc774 \uba87\uba85\uc778\uc9c0 \uc54c\uc544\ubcf4\uace0\uc790 \ud55c\ub2e4\uba74 ?<\/p>\n

<\/p>\n

SELECT  Level  , count (*)<\/p>\n

FROM     emp<\/p>\n

CONNECT  BY   PRIOR  empno = mgr<\/p>\n

START  WITH   mgr IS NULL <\/p>\n

GROUP BY  level ;<\/p>\n

<\/p>\n

<\/p>\n

===========  \uacb0\uacfc\ub97c\ubcf4\uba74 ============<\/p>\n

<\/p>\n

     LEVEL   COUNT(*)<\/p>\n

———- ———-<\/p>\n

           1          1<\/p>\n

           2          3<\/p>\n

           3          8<\/p>\n

           4          2<\/p>\n

<\/p>\n

\uac01 \ub808\ubca8\ubcc4\ub85c \uba87\uba85\uc774 \uc874\uc7ac\ud558\ub294\uc9c0 \uc54c \uc218 \uc788 \ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"

\uacc4\uce35\ud615 \ub370\uc774\ud130 \ud45c\ud604\uc774\ub780 ? \uac04\ub2e8\ud558\uac8c \uc124\uba85\ud558\uc790\uba74 \uc0c1\uc704 \uad6c\uc870\uac00 \uc788\uace0 \ud558\uc704\uad6c\uc870\uac00 \uc874\uc7ac\ud560\ub54c\uc5d0 \uadf8 \uad6c\uc870\ub97c \ud45c\ud604\ud558\ub294\uac83 \uc774\uac8c \ub354 \uc5b4\ub824\uc6b8\uc218 \uc788\uc73c\ub2c8 \uc608\ub97c \ub4e4\uc790\uba74 \ud68c\uc0ac\ub85c \ub530\uc9c0\uba74 \uc0ac\uc7a5\uc774 \uc874\uc7ac\ud558\uace0 \ubd80\uc0ac\uc7a5\uc774 \uc874\uc7ac\ud558\uace0 \uac01 \ubd80\uc11c\uc758 \uc9f1\ub4e4\uc774 \uc874\uc7ac\ud560\ud150\ub370 \uadf8\uac78 \ub098\ud0c0\ub0b4\ubcf4\uba74 \uc0ac\uc7a5       \ubd80\uc0ac\uc7a5                   \ucd1d\ubb34\ubd80\uc9f1                 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[132],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/407"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=407"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/407\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=407"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=407"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}