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의 정보값을 이어붙이고 싶은건데..ㅜㅜ 어렵네요.. 도와주세요...