[SQL] CONNECT BY = WITH

Posted on 2012. 1. 12. 10:17
Filed Under DB

계층적인 구조를 만들때 사용(recursive)
or COLUMN을 ROW로 쪼갤때도 사용(검색Key: column to row)

SELECT TO_CHAR(TO_DATE('201201','YYYYMM')+LEVEL-1, 'YYYYMMDD') AS DT,
   TO_CHAR(TO_DATE('201201','YYYYMM')+LEVEL-1, 'DAY') AS WEEK
FROM DUAL
CONNECT BY LEVEL <= TRUNC(TO_DATE('201201','YYYYMM')+32,'MM')-TO_DATE('201201','YYYYMM');

DT         WEEK
20120101 SUNDAY
20120102 MONDAY
20120103 TUESDAY
20120104 WEDNESDAY
20120105 THURSDAY
20120106 FRIDAY
20120107 SATURDAY
20120108 SUNDAY
20120109 MONDAY
20120110 TUESDAY
20120111 WEDNESDAY
20120112 THURSDAY
20120113 FRIDAY
20120114 SATURDAY
20120115 SUNDAY
20120116 MONDAY
20120117 TUESDAY
20120118 WEDNESDAY
20120119 THURSDAY
20120120 FRIDAY
20120121 SATURDAY
20120122 SUNDAY
20120123 MONDAY
20120124 TUESDAY
20120125 WEDNESDAY
20120126 THURSDAY
20120127 FRIDAY
20120128 SATURDAY
20120129 SUNDAY
20120130 MONDAY
20120131 TUESDAY

<출처: http://hermestop.tistory.com/145>

<more: http://www.gurubee.net/lecture/1903>