데이터베이스 질문과 답변
주말 잘보내고 계시는지 모르겠습니다..
전 역량부족으로 주말에도 열일하고있는데요.. 너무 어렵습니다.. 도움 부탁 드리겠습니다!
여쭤볼 내용은 구분값에 따라 한열에 쭉 보이고 싶게 하는건데요 잘안되네요...
구분 값은 FLAG 로 각각 PF,PC,DF,DC로 나뉘며 이구분값에 따라
열구성이
PF /PF_CNT/_ PF_AMT/ PC/ PC_CNT / PC_AMT / DF/ DF_CNT /DF_AMT/ DC/ DC_CNT/ DC_AMT 순으로 쭉 나와야됩니다..
쿼리는 아래와 같습니다..
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SELECT DECODE(FLAG,'PF',A.PRNT_NM) PF , COUNT(DECODE(FLAG,'PF',1)) PF_CNT , SUM(DECODE(FLAG,'PF',B.PAY_ITEM_AMT)) PF_AMT , DECODE(FLAG,'PC',A.PRNT_NM) PC , COUNT(DECODE(FLAG,'PC',1)) PC_CNT , SUM(DECODE(FLAG,'PC',B.PAY_ITEM_AMT)) PC_AMT , DECODE(FLAG,'DF',A.PRNT_NM) DF , COUNT(DECODE(FLAG,'DF',1)) DF_CNT , SUM(DECODE(FLAG,'DF',B.PAY_ITEM_AMT)) DF_AMT , DECODE(FLAG,'DC',A.PRNT_NM) DC , COUNT(DECODE(FLAG,'DC',1)) DC_CNT , SUM(DECODE(FLAG,'DC',B.PAY_ITEM_AMT)) DC_AMT , FLAG FROM (SELECT PAY_ITEM_CD , PRNT_NM , CASE WHEN PAY_ITEM_CD <= 1160 THEN 'PF' WHEN PAY_ITEM_CD > 1160 AND PAY_ITEM_CD <= 1330 THEN 'PC' WHEN PAY_ITEM_CD > 1330 AND PAY_ITEM_CD <= 2180 THEN 'DF' WHEN PAY_ITEM_CD > 2180 AND PAY_ITEM_CD <= 2381 THEN 'DC' END AS FLAG FROM MPB_PAYITEM) A , MPP_PAYITEMPYDTL B WHERE A.PAY_ITEM_CD = B.PAY_ITEM_CD(+) AND PY_YM(+) BETWEEN '201610' AND '201610' GROUP BY A.PAY_ITEM_CD , A.PRNT_NM |
결과값은 예상 가시겠지만
PF /PF_CNT/_ PF_AMT/ PC/ PC_CNT / PC_AMT / DF/ DF_CNT /DF_AMT/ DC/ DC_CNT/ DC_AMT
DATA1/DATA1/DATA1/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL
DATA1/DATA1/DATA1/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL
DATA1/DATA1/DATA1/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL
/NULL/NULL/NULL/DATA2/DATA2/DATA2/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL
/NULL/NULL/NULL/DATA2/DATA2/DATA2/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL
/NULL/NULL/NULL/DATA2/DATA2/DATA2/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/DATA3/DATA3/DATA3/NULL/NULL/NULL//NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/DATA3/DATA3/DATA3/NULL/NULL/NULL//NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/DATA3/DATA3/DATA3/NULL/NULL/NULL//NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/DATA4/DATA4/DATA4/NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/DATA4/DATA4/DATA4/NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/DATA4/DATA4/DATA4/NULL/NULL/NULL
/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/DATA5/DATA5/DATA5
/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/DATA5/DATA5/DATA5
/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/NULL/DATA5/DATA5/DATA5
이런식으로 출력됩니다...원하는 결과 셋은
DATA1/DATA1/DATA1/DATA2/DATA2/DATA2/DATA3/DATA3/DATA3/DATA4/DATA4/DATA4/DATA5/DATA5/DATA5/
DATA1/DATA1/DATA1/DATA2/DATA2/DATA2/DATA3/DATA3/DATA3/DATA4/DATA4/DATA4/DATA5/DATA5/DATA5/
DATA1/DATA1/DATA1/DATA2/DATA2/DATA2/DATA3/DATA3/DATA3/DATA4/DATA4/DATA4/DATA5/DATA5/DATA5/
DATA1/DATA1/DATA1/DATA2/DATA2/DATA2/DATA3/DATA3/DATA3/DATA4/DATA4/DATA4/DATA5/DATA5/DATA5/ .
위와 같은 식으로 나오길 원합니다 ... 감사합니다
아래는 현제 결과값입니다
핸드폰이라 다충봤는데 아웃터 조인을 밖으로(인라인뷰) 빼셔야 할것 같네요
답변 감사드립니다! 아우터 조인은 이미 밖으로 빠져있는데 혹시 다른 의미가 잇나용?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SELECT MIN(DECODE(flag, 'PF', pnm)) pf , MIN(DECODE(flag, 'PF', cnt)) pf_cnt , MIN(DECODE(flag, 'PF', amt)) pf_amt , MIN(DECODE(flag, 'PC', pnm)) pc , MIN(DECODE(flag, 'PC', cnt)) pc_cnt , MIN(DECODE(flag, 'PC', amt)) pc_amt , MIN(DECODE(flag, 'DF', pnm)) df , MIN(DECODE(flag, 'DF', cnt)) df_cnt , MIN(DECODE(flag, 'DF', amt)) df_amt , MIN(DECODE(flag, 'DC', pnm)) dc , MIN(DECODE(flag, 'DC', cnt)) dc_cnt , MIN(DECODE(flag, 'DC', amt)) dc_amt FROM (SELECT a.pay_item_cd , a.prnt_nm AS pnm , a.flag , COUNT(b.pay_ym) cnt , SUM(b.pay_item_amt) amt , ROW_NUMBER() OVER(PARTITION BY a.flag ORDER BY a.pay_item_cd) rn FROM (SELECT pay_item_cd , prnt_nm , CASE WHEN pay_item_cd <= 1160 THEN 'PF' WHEN pay_item_cd > 1160 AND pay_item_cd <= 1330 THEN 'PC' WHEN pay_item_cd > 1330 AND pay_item_cd <= 2180 THEN 'DF' WHEN pay_item_cd > 2180 AND pay_item_cd <= 2381 THEN 'DC' END AS flag FROM mpb_payitem ) a , mpp_payitempydtl b WHERE a.pay_item_cd = b.pay_item_cd(+) AND py_ym(+) BETWEEN '201610' AND '201610' GROUP BY a.pay_item_cd, a.prnt_nm, a.flag ) GROUP BY rn ORDER BY rn; |
답변 감사합니다!! 아주 잘됩니다 ㅠㅠ 정말 감사합니다 또배워갑니다
) 버튼을 클릭하여 작성 하시면 됩니다.