SELECT *
FROM
(select tmp1.cmpy_no 
    , tmp1.CMPY_NM
    , tmp1.ITRL_TP_CD
    , (select CD_NM from TCOMM_CD_DTL DT where GRP_CD='CM003' and CD = tmp1.ITRL_TP_CD) ITRL_TP_CD_NM
    , count(1) allcount
    , sum(decode(MBR_PASWD, null, 0, 1)) logincount
    , sum(decode(tmp2.mbr_no , null, 0, 1)) APP_CNT
    , sum(case when tmp2.mbr_no is not null and apsh_tkn is not null and  login_dts > to_date('2018-03-15 000000', 'yyyy-mm-dd hh24miss') then 1 else 0 end ) PUSH_APP_VER_CNT
    , sum(case when tmp2.mbr_no is not null and apsh_tkn is not null and  login_dts > to_date('2018-03-15 000000', 'yyyy-mm-dd hh24miss') AND tmp2.APSH_RCV_YN = 'Y' then 1 else 0 end ) PUSH_RCV_APP_VER_CNT
    from 
    (select a.cmpy_no, B.CMPY_NM, a.mbr_no , a.MBR_ID, a.MBR_PASWD
    , nvl(SMS_RCV_YN , 'N') SMS_RCV_YN
    , B.ITRL_TP_CD
    , nvl(c1.app_id, nvl(c2.app_id, 'benecafe')) app_id
    from tmbr_bas a
        , tcmpy_cmpy_bas b 
        , tcomm_app_inf c1
        , tcomm_app_inf c2
    where a.cmpy_no = b.cmpy_no 
    and b.cmpy_no = c1.cmpy_no (+)
    and b.ITRL_TP_CD = c2.ITRL_TP_CD (+)
    and nvl(login_lck_yn, 'N') = 'N' 
    and nvl(SRE_TP_CD, '10') = '10'
    and nvl(CMPY_STAT_CD, '20') = '20'
    ) tmp1
    ,(select x.cmpy_no, mbr_no, pgm_id, APSH_RCV_YN, login_dts, apsh_tkn
    from tmbr_mbr_mobl_info_lst x
    where x.login_dts = (select max(login_dts) from tmbr_mbr_mobl_info_lst y where x.cmpy_no = y.cmpy_no and x.mbr_no = y.mbr_no)
    and x.REG_DTS BETWEEN TO_DATE('2018-11-16' || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2018-11-22' || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    ) tmp2
    where tmp1.cmpy_no = tmp2.cmpy_no(+)
    and tmp1.mbr_no = tmp2.mbr_no(+)
    AND tmp1.CMPY_NO != 'ABK'
    group by tmp1.CMPY_NM, tmp1.cmpy_no,tmp1.ITRL_TP_CD, app_id
) cmp1
LEFT JOIN (SELECT a.CMPY_NO
,a.ONL_PNT_USE_YN AS
,sum(b.PRVE_PN
,sum(b.ASSN_PNT)
,sum(b.USE_PNT)
,sum(b.EXR_PNT)
,ROUND(AVG(b.LBYY),1)
From TWPNT_BAS A
LEFT JOIN TWPNT_BAS b ON a.CMPY_NO = b.CMPY_NO
GROUP BY a.CMPY_NO, a.ONL_PNT_USE_YN)cmp2
on cmp1.cmpy_no = cmp2.cmpy_no;

고수님들 질문드립니다..

아래 쿼리에서... cmp1 테이블 기준으로 데이터값을 보고 싶어서.. LEFT JOIN을 하였는데..

결과값을 보니... 동일한 데이터가 2개씩 나왔더라구요.. (cmpy_no기준 2개씩..)

제가 보고자하는 결과는 cmp1결과값을 토대로 cmpy_no번호가 일치하는 것들만

cmp2의 정보값을 이어붙이고 싶은건데..ㅜㅜ 어렵네요.. 도와주세요...