원본 쿼리 입니다.
SELECT *
FROM (
SELECT
(select MBER_NM
from WRKINNOV_CONSULT_MNG mng
where mng.CONSULTING_ID = a.CONSULTING_ID) as mng_MBER_NM -- 사업장관리책임자
FROM apple a /*일터혁신:컨설팅신청*/
INNER JOIN orange CR /*공통-신청*/
ON CR.REQST_CM_NO = a.REQST_CM_NO
INNER JOIN CO_SRVC CS /*공통-서비스*/
ON CS.SRVC_NO = CR.SRVC_NO
AND CS.REQST_FORM_CODE IN ('30', '31')
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY REQST_CM_NO ORDER
BY REGIST_DT DESC NULLS LAST) RN,
A.* FROM CO_APP_CHARGER A WHERE CHARGER_SE_CODE = '9') c --신청담당자정보
ON (c.RN = '1' AND c.REQST_CM_NO = a.REQST_CM_NO)
LEFT OUTER JOIN CO_REQUEST_CMPNY_INFO d /*공통-신청기업정보*/
ON d.REQST_CM_NO = a.REQST_CM_NO
AND d.CMPNY_NO = CR.CMPNY_NO
LEFT OUTER JOIN CO_REQST_CMPNY_YEARLY_INFO e /*신청기업년도별정보*/
ON e.REQST_CM_NO = a.REQST_CM_NO
AND e.CMPNY_NO = CR.CMPNY_NO
AND e.YEAR = CS.SRVC_YEAR - 1
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY REQST_CM_NO ORDER
BY REGIST_DT DESC NULLS LAST) RN,
A.* FROM CO_REQST_CMPNY_UNION_INFO A) f --신청기업노조정보
ON (f.RN = '1' AND f.REQST_CM_NO = a.REQST_CM_NO)
LEFT OUTER JOIN WRKINNOV_CONSULT_ADD_INFO Z
ON Z.CONSULTING_ID = a.CONSULTING_ID
WHERE 1=1
ORDER BY A.CONSULTING_ID DESC
) ww
WHERE 1=1
원하는 결과표는 최초로 등록된 사업장관리책임자만 가지고 오고 싶은것입니다.
DB는 오라클 11g 쓰고 있습니다.