oracle > mysql 쿼리문 0 3 188

by 구웃 [MySQL] [2019.10.08 17:56:23]


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에서 돌아가게끔 변경좀 부탁드립니다

 

 

 

 

by 마농 [2019.10.08 18:31:41]

MySQL 버전을 알려주세요.


by 마농 [2019.10.10 09:06:04]

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
;

 


by 구웃 [2019.10.10 11:40:24]

감사합니다 좋은하루되세요!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입