데이터베이스 질문과 답변
제가 짠 sql 은 아래와 같습니다.
SELECT ORG.ORG_CODE
, ORG.ORG_NM
, ORG.VALID_YN
, ORG.RPRSNTV_NM
, ORG.BIZRNO
, ORG.TELNO
, ORG.NATION
, ORG.LAW_TYPE
, ORG.DECIPHER_TYPE
, ORG.REPORT_TYPE
, ORG.BIZ_TYPE
, ORG.BASE_ADRES
, ORG.DTL_ADRES
, ORG.POSTNO
, ORG.SUB_ORG_MGMT_YN
, ORG.SUB_ORG_TYPE
, ORG.APPRVL_YN
, ORG.APPRVL_DT
, ORG_AMD_NO
, ORG.UP_ORG_CODE
, ORG.REGION
, ORG.OUTSRC_ORG_MGMT_YN
, ORG.ATCH_FILE_ID
, ORG.FRST_REGIST_PNTTM
, ORG.FRST_REGISTER_ID
, ORG.LAST_UPDT_PNTTM
, ORG.LAST_UPDUSR_ID
, ORG.PRMISN_MGMT_YN
, ORG.BIZ_CAT
, ORG.ENG_ORG_NM
, null AS IS_REGISTED_CNT
,(SELECT WM_CONCAT(PRMISN_SE) AS PRMISN_SE FROM PRMISN
WHERE PRMISN_SE IS NOT NULL AND ORG_CODE = ORG.ORG_CODE) AS PRMISN_SE
,(SELECT WM_CONCAT(DCLRT_TYPE) AS DCLRT_TYPE FROM PRMISN
WHERE DCLRT_TYPE IS NOT NULL AND ORG_CODE = ORG.ORG_CODE) AS DCLRT_TYPE
,(SELECT WM_CONCAT(TYPE) AS TYPE FROM PRMISN_TYPE
WHERE ORG_CODE = ORG.ORG_CODE) AS TYPE
, USERS.LAST_NM
, USERS.FIRST_NM
, USERS.EMAIL
, USERS.MOBILENO
FROM ORG ORG
LEFT OUTER JOIN AGENT_MGR AM ON AM.ORG_CODE = ORG.ORG_CODE AND AM.RPRSNTV_YN = 'Y'
LEFT OUTER JOIN USERS USERS ON USERS.ESNTL_ID = AM.ESNTL_ID
문제는 데이터가 많아지면서 속도가 올라갓다는 건데요. 서브 쿼리들 때문에 그러더라구요.
wm_concat 으로 다른테이블에 있는 항목을 전부 불러와야하는데...
위와 같은 쿼리를 좀 간략하게 만드는 방법 없을까요 속도 빨라지게요
부탁드립니다.
SELECT org.org_code , org.org_nm , org.valid_yn , org.rprsntv_nm , org.bizrno , org.telno , org.nation , org.law_type , org.decipher_type , org.report_type , org.biz_type , org.base_adres , org.dtl_adres , org.postno , org.sub_org_mgmt_yn , org.sub_org_type , org.apprvl_yn , org.apprvl_dt , org_amd_no , org.up_org_code , org.region , org.outsrc_org_mgmt_yn , org.atch_file_id , org.frst_regist_pnttm , org.frst_register_id , org.last_updt_pnttm , org.last_updusr_id , org.prmisn_mgmt_yn , org.biz_cat , org.eng_org_nm , null AS is_registed_cnt , p.prmisn_se , p.dclrt_type , pt.type , users.last_nm , users.first_nm , users.email , users.mobileno FROM org LEFT OUTER JOIN agent_mgr am ON am.org_code = org.org_code AND am.rprsntv_yn = 'Y' LEFT OUTER JOIN users ON users.esntl_id = am.esntl_id LEFT OUTER JOIN (SELECT org_code , wm_concat(prmisn_se) AS prmisn_se , wm_concat(dclrt_type) AS dclrt_type FROM prmisn WHERE prmisn_se IS NOT NULL OR dclrt_type IS NOT NULL GROUP BY org_code ) p ON p.org_code = org.org_code LEFT OUTER JOIN (SELECT org_code , wm_concat(type) AS type FROM prmisn_type GROUP BY org_code ) pt ON pt.org_code = org.org_code ;
감사합니다~
) 버튼을 클릭하여 작성 하시면 됩니다.