SELECT T1.* FROM ( SELECT ROWNUM RNUM, T.* FROM ( SELECT A.LCTRE_KEY AS lctreKey , A.INSTT_CODE AS insttCode , A.LCTRE_LCLAS AS lctreLclas , A.LCTRE_NM AS lctreNm , A.FREE_YN AS freeYn , NVL(A.ATNCL_CT, 0) AS atnclCt , A.PRIOR_RCEPT_BGNDE AS priorRceptBgnde , A.PRIOR_RCEPT_ENDDE AS priorRceptEndde , A.RCEPT_BGNDE AS rceptBgnde , A.RCEPT_ENDDE AS rceptEndde , A.EDC_BGNDE AS edcBgnde , A.EDC_ENDDE AS edcEndde , A.ADIT_RCEPT_BGNDE AS aditRceptBgnde , A.ADIT_RCEPT_ENDDE AS aditRceptEndde , A.RCEPT_MTH AS rceptMth , A.RCEPT_STTUS AS rceptSttus , A.EDC_STTUS AS edcSttus , A.USE_AT AS useAt , A.CANCEL_YN AS cancelYn , A.LCTRE_CTGRY AS lctreCtgry , A.RCEPT_STTUS_AUTO_AT AS rceptSttusAutoAt , NVL(A.RCRIT_NMPR,0) AS rcritNmpr , NVL(A.ONLINE_NMPR, 0) AS onlineNmpr , NVL(A.OFFLINE_NMPR, 0) AS offlineNmpr , NVL(A.WAIT_NMPR, 0) AS waitNmpr , NVL(B.STTUS00, 0) AS sttus00 , NVL(B.STTUS01, 0) AS sttus01 , NVL(B.STTUS02, 0) AS sttus02 , NVL(B.STTUS03, 0) AS sttus03 , NVL(B.STTUS04, 0) AS sttus04 , NVL(B.STTUS09, 0) AS sttus09 , NVL(B.STTUS10, 0) AS sttus10 , A.LCTRE_INTRCN AS lctreIntrcn ,A.RCRIT_MTH AS rcritMth FROM TN_EDC_LCTRE A , ( SELECT LCTRE_KEY , SUM(S00) AS STTUS00 , SUM(S01) AS STTUS01 , SUM(S02) AS STTUS02 , SUM(S03) AS STTUS03 , SUM(S04) AS STTUS04 , SUM(S09) AS STTUS09 , SUM(S10) AS STTUS10 FROM ( SELECT LCTRE_KEY ,CASE WHEN STTUS = '00' THEN CNT END AS S00 ,CASE WHEN STTUS = '01' THEN CNT END AS S01 ,CASE WHEN STTUS = '02' THEN CNT END AS S02 ,CASE WHEN STTUS = '03' THEN CNT END AS S03 ,CASE WHEN STTUS = '04' THEN CNT END AS S04 ,CASE WHEN STTUS = '09' THEN CNT END AS S09 ,CASE WHEN STTUS = '10' THEN CNT END AS S10 FROM ( SELECT AA.LCTRE_KEY, SUM(BB.EDC_NMPR) CNT, BB.STTUS FROM TN_EDC_ATNLC_MNGR AA, TN_EDC_ATNLC_USER BB WHERE AA.ATNCL_KEY = BB.ATNCL_KEY AND AA.LCTRE_KEY IN ( SELECT LCTRE_KEY FROM ( SELECT ROWNUM RNUM, LCTRE_KEY FROM ( SELECT LCTRE_KEY FROM TN_EDC_LCTRE A WHERE 1=1 ORDER BY EDC_BGNDE DESC , EDC_ENDDE DESC, A.LCTRE_KEY DESC ) TT WHERE ROWNUM <= 10 ) TT1 WHERE RNUM > 0 ) GROUP BY AA.LCTRE_KEY, BB.STTUS ) ) GROUP BY LCTRE_KEY ) B WHERE A.LCTRE_KEY = B.LCTRE_KEY(+) ORDER BY EDC_BGNDE DESC , EDC_ENDDE DESC, A.LCTRE_KEY DESC ) T WHERE ROWNUM <= 10 ) T1 WHERE RNUM > 0
안녕하세요 위 쿼리문 mysql에서 돌아가게끔 변경좀 부탁드립니다
rnum 이 페이징 처리용으로만 사용되고 조회되지 않아도 된다면?
LIMIT 구문을 이용하시면 됩니다.
SELECT a.lctre_key AS lctrekey , a.instt_code AS insttcode , a.lctre_lclas AS lctrelclas , a.lctre_nm AS lctrenm , a.free_yn AS freeyn , IFNULL(a.atncl_ct, 0) AS atnclct , a.prior_rcept_bgnde AS priorrceptbgnde , a.prior_rcept_endde AS priorrceptendde , a.rcept_bgnde AS rceptbgnde , a.rcept_endde AS rceptendde , a.edc_bgnde AS edcbgnde , a.edc_endde AS edcendde , a.adit_rcept_bgnde AS aditrceptbgnde , a.adit_rcept_endde AS aditrceptendde , a.rcept_mth AS rceptmth , a.rcept_sttus AS rceptsttus , a.edc_sttus AS edcsttus , a.use_at AS useat , a.cancel_yn AS cancelyn , a.lctre_ctgry AS lctrectgry , a.rcept_sttus_auto_at AS rceptsttusautoat , IFNULL(a.rcrit_nmpr , 0) AS rcritnmpr , IFNULL(a.online_nmpr , 0) AS onlinenmpr , IFNULL(a.offline_nmpr, 0) AS offlinenmpr , IFNULL(a.wait_nmpr , 0) AS waitnmpr , IFNULL(b.sttus00, 0) AS sttus00 , IFNULL(b.sttus01, 0) AS sttus01 , IFNULL(b.sttus02, 0) AS sttus02 , IFNULL(b.sttus03, 0) AS sttus03 , IFNULL(b.sttus04, 0) AS sttus04 , IFNULL(b.sttus09, 0) AS sttus09 , IFNULL(b.sttus10, 0) AS sttus10 , a.lctre_intrcn AS lctreintrcn , a.rcrit_mth AS rcritmth FROM (SELECT lctre_key , instt_code , lctre_lclas , lctre_nm , free_yn , atncl_ct , prior_rcept_bgnde , prior_rcept_endde , rcept_bgnde , rcept_endde , edc_bgnde , edc_endde , adit_rcept_bgnde , adit_rcept_endde , rcept_mth , rcept_sttus , edc_sttus , use_at , cancel_yn , lctre_ctgry , rcept_sttus_auto_at , rcrit_nmpr , online_nmpr , offline_nmpr , wait_nmpr , lctre_intrcn , rcrit_mth FROM tn_edc_lctre ORDER BY edc_bgnde DESC, edc_endde DESC, lctre_key DESC LIMIT 0, 10 ) a LEFT OUTER JOIN (SELECT a.lctre_key , COUNT(CASE bb.sttus WHEN '00' THEN 1 END) sttus00 , COUNT(CASE bb.sttus WHEN '01' THEN 1 END) sttus01 , COUNT(CASE bb.sttus WHEN '02' THEN 1 END) sttus02 , COUNT(CASE bb.sttus WHEN '03' THEN 1 END) sttus03 , COUNT(CASE bb.sttus WHEN '04' THEN 1 END) sttus04 , COUNT(CASE bb.sttus WHEN '09' THEN 1 END) sttus09 , COUNT(CASE bb.sttus WHEN '10' THEN 1 END) sttus10 FROM (SELECT lctre_key FROM tn_edc_lctre ORDER BY edc_bgnde DESC, edc_endde DESC, lctre_key DESC LIMIT 0, 10 ) a INNER JOIN tn_edc_atnlc_mngr aa ON aa.lctre_key = a.lctre_key INNER JOIN tn_edc_atnlc_user bb ON aa.atncl_key = bb.atncl_key GROUP BY a.lctre_key ) b ON a.lctre_key = b.lctre_key ;