오라클 -> MySQL 쿼리 변환하기

오라클 쿼리 :

WITH DIV_SCOPE AS (
  SELECT #{agoYr} || '1201' AS FRST_DT, TO_CHAR(LAST_DAY(TO_DATE(#{basYr} || '02', 'YYYYMM)), 'YYYYMMDD') AS LAST_DT, 1 AS DIV_NO FROM DUAL
  UNION
  SELECT #{basYr} || '0301' AS FRST_DT, TO_CHAR(LAST_DAY(TO_DATE(#{basYr} || '05', 'YYYYMM)), 'YYYYMMDD') AS LAST_DT, 2 AS DIV_NO FROM DUAL
  UNION
  SELECT #{basYr} || '0601' AS FRST_DT, TO_CHAR(LAST_DAY(TO_DATE(#{basYr} || '08', 'YYYYMM)), 'YYYYMMDD') AS LAST_DT, 3 AS DIV_NO FROM DUAL
  UNION
  SELECT #{basYr} || '0901' AS FRST_DT, TO_CHAR(LAST_DAY(TO_DATE(#{basYr} || '11', 'YYYYMM)), 'YYYYMMDD') AS LAST_DT, 4 AS DIV_NO FROM DUAL
)
SELECT *
  FROM DIV_SCOPE
 WHERE DIV_NO = (
           SELECT MAX(DIV_NO)
             FROM DIV_SCOPE
            WHERE TO_CHAR(SYSDATE, 'YYYYMMDD') >= FRST_DT
              AND TO_CHAR(SYSDATE, 'YYYYMMDD') >= LAST_DT)

 

MySQL 쿼리 :

(8.0 버전 이후부터는 WITH 쿼리문을 지원하지 않음)

WITH DIV_SCOPE AS (
	SELECT CONCAT(#{agoYr},'1201') AS FRST_DT, DATE_FORMAT(LAST_DAY( CONCAT(#{basYr}, '0201') ), '%Y%m%d') AS LAST_DT, 1 AS DIV_NO
	UNION
	SELECT CONCAT(#{basYr},'0301') AS FRST_DT, DATE_FORMAT(LAST_DAY( CONCAT(#{basYr}, '0501') ), '%Y%m%d') AS LAST_DT, 2 AS DIV_NO
	UNION
	SELECT CONCAT(#{basYr},'0601') AS FRST_DT, DATE_FORMAT(LAST_DAY( CONCAT(#{basYr}, '0801') ), '%Y%m%d') AS LAST_DT, 3 AS DIV_NO
	UNION
	SELECT CONCAT(#{basYr},'0901') AS FRST_DT, DATE_FORMAT(LAST_DAY( CONCAT(#{basYr}, '1101') ), '%Y%m%d') AS LAST_DT, 4 AS DIV_NO
)
SELECT *
  FROM DIV_SCOPE
 WHERE DIV_NO = (SELECT MAX(DIV_NO)
 				   FROM DIV_SCOPE
 				  WHERE DATE_FORMAT(NOW(), '%Y%m%d') >= FRST_DT
 				    AND DATE_FORMAT(NOW(), '%Y%m%d') >= LAST_DT
 				)