하은양 믿음군 효실맘 호홍홍집s

Oracle 시설예외시간을 MySql로 불러오기 Subquery 본문

가벼운 배움/MySql

Oracle 시설예외시간을 MySql로 불러오기 Subquery

호홍홍집 2016. 12. 21. 15:09

Oracle :

sSql = "SELECT CALES.DATES,TO_CHAR(TO_DATE(CALES.DATES,'YYYYMMDD'),'D') DATE_WEEK,EXP_SEQ,SDATE,STIME,EDATE,ETIME,TITLE,NAME " +

            "FROM ( " +

            "   SELECT TO_CHAR(TO_DATE('"+sFirstDate+"','YYYYMMDD') + ROWNUM - 1,'YYYYMMDD') DATES " +

            "   FROM ALL_OBJECTS " +

            "   WHERE ROWNUM <= (LAST_DAY(TO_DATE('"+sFirstDate+"','YYYYMMDD')) - TO_DATE('"+sFirstDate+"','YYYYMMDD')) +1 " +

            ") CALES, " +

            "(  " +

            "   SELECT DATES,EXP_SEQ,SDATE,STIME,EDATE,ETIME,TITLE,NAME " +

            "   FROM ( " +

            "       SELECT " +

            "           IEXP.EXP_SEQ, " +

            "           TO_CHAR(IEXP.SDATE,'YYYYMMDD') SDATE, TO_CHAR(IEXP.SDATE,'HH24:MI') STIME, " +

            "           TO_CHAR(IEXP.EDATE,'YYYYMMDD') EDATE, TO_CHAR(IEXP.EDATE,'HH24:MI') ETIME, " +

            "           IEXP.TITLE,ITM.NAME   " +

            "       FROM TB_ITEMS_EXP IEXP, TB_ITEMS ITM " +

            "       WHERE IEXP.ITEM_ID = ITM.ITEM_ID " +

            "   ) TB_EXP, " +

            "   ( " +

            "       SELECT TO_CHAR(TO_DATE('"+sFirstDate+"','YYYYMMDD') + ROWNUM - 1,'YYYYMMDD') DATES " +

            "       FROM ALL_OBJECTS " +

            "       WHERE ROWNUM <= (LAST_DAY(TO_DATE('"+sFirstDate+"','YYYYMMDD')) - TO_DATE('"+sFirstDate+"','YYYYMMDD')) +1 " +

            "   ) CAL " +

            "   WHERE CAL.DATES BETWEEN TB_EXP.SDATE AND TB_EXP.EDATE " +

            "   ORDER BY CAL.DATES, TB_EXP.SDATE " +

            ") CAL_TB_EXP " +

            "WHERE CALES.DATES = CAL_TB_EXP.DATES(+) " +

            "ORDER BY CALES.DATES,STIME ";


MySql :     

sSql = "SELECT CALES.DATES, DAYOFWEEK(STR_TO_DATE(CALES.DATES,'%Y%m%d')) DATE_WEEK, EXP_SEQ, SDATE, STIME, EDATE, ETIME, TITLE, NAME " +

"FROM ( " +

"   SELECT DATE_FORMAT(TDATE,'%Y%m%d') DATES " +

"   FROM TB_BIZDAY " +

"   WHERE TDATE >= STR_TO_DATE('"+sFirstDate+"','%Y%m%d') AND TDATE <= LAST_DAY(STR_TO_DATE('"+sFirstDate+"','%Y%m%d')) " +

") CALES LEFT OUTER JOIN " +

"(  " +

" SELECT DATES,EXP_SEQ,SDATE,STIME,EDATE,ETIME,TITLE,NAME " +

"   FROM ( " +

"       SELECT IEXP.EXP_SEQ, IEXP.TITLE, ITM.NAME, " +

" DATE_FORMAT(IEXP.SDATE,'%Y%m%d') SDATE, DATE_FORMAT(IEXP.SDATE,'%H%i') STIME, " +

" DATE_FORMAT(IEXP.EDATE,'%Y%m%d') EDATE, DATE_FORMAT(IEXP.EDATE,'%H%i') ETIME " +

"       FROM TB_ITEMS_EXP IEXP, TB_ITEMS ITM " +

" WHERE IEXP.ITEM_ID = ITM.ITEM_ID " +

"   ) TB_EXP, " +

"   ( " +

"       SELECT DATE_FORMAT(TDATE,'%Y%m%d') DATES " +

" FROM TB_BIZDAY " +

" WHERE TDATE >= STR_TO_DATE('"+sFirstDate+"','%Y%m%d') AND TDATE <= LAST_DAY(STR_TO_DATE('"+sFirstDate+"','%Y%m%d'))  " +

"   ) CAL " +

"   WHERE CAL.DATES BETWEEN TB_EXP.SDATE AND TB_EXP.EDATE " +

" ORDER BY CAL.DATES, TB_EXP.SDATE " +

") CAL_TB_EXP " +

" ON CALES.DATES = CAL_TB_EXP.DATES " +

"ORDER BY CALES.DATES,STIME ";

'가벼운 배움 > MySql' 카테고리의 다른 글

여러 테이블 left outer join 처리하기  (0) 2016.12.26
Mysql trigger 사용 예제  (0) 2016.12.23
Oracle 임의 키생성을 Mysql로 처리  (0) 2016.12.21
2개 Table Join 해서 한 테이블의 값을 UPDATE  (0) 2016.04.07
Mysql union all이용한 시간단위 값가져오기 편법 ㅋㅋ  (0) 2016.02.26
'가벼운 배움/MySql' Related Articles more