데이터베이스 질문과 답변
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.