도와주세요 ㅠ 0 3 191

by 스티브1조 [2018.03.12 18:55:08]


SELECT  T1.event_dt, T1.sex, T1.age, T1.area_nm, T1.gift_nm, T1.evt_win_cnt, T2.give_cnt
FROM
			(
			/* 단무절 당첨자수  */
			SELECT a.EVENT_DT,
				  		DECODE(b.sex,'M','남자','W','여자','기타')                    AS sex,
			        ( TO_char(sysdate,'YYYY') - TO_NUMBER(b.birth_yyyy) + 1 ) AS age,
				  		CASE WHEN LENGTH(TRIM(b.post_Cd))=5 THEN  f_area_nm('FIVE',SUBSTR(b.post_Cd,1,2))
			             WHEN LENGTH(TRIM(b.post_Cd))=6 THEN  f_area_nm('SIX', SUBSTR(b.post_Cd,1,2))
			             ELSE '기타'
			        END                        AS area_nm,
			        f_april_gift_nm(a.GIFT_CD) AS gift_nm,
			        COUNT(*)                   AS  evt_win_cnt
			  FROM EVT_APRILDAY_WIN a,
			       member_info      b
			  WHERE a.MEMBER_NO = b.MEMBER_NO(+)
			  GROUP BY a.EVENT_DT, b.sex, b.birth_yyyy,  b.POST_CD, a.GIFT_CD
			  ORDER BY a.EVENT_DT, b.sex, b.birth_yyyy,  b.POST_CD, a.GIFT_CD
			) T1,
			(
			/* 만우절경품수령자수  */
			 SELECT TO_CHAR(b.GIFT_GIVE_DTM,'YYYYMMDD') AS event_dt,
			        --b.MEMBER_NO,
			        DECODE(c.sex,'M','남자','W','여자','기타')                    AS sex,
			        ( TO_char(sysdate,'YYYY') - TO_NUMBER(c.birth_yyyy) + 1 ) AS age,
				  		CASE WHEN LENGTH(TRIM(c.post_Cd))=5 THEN  f_area_nm('FIVE',SUBSTR(c.post_Cd,1,2))
			             WHEN LENGTH(TRIM(c.post_Cd))=6 THEN  f_area_nm('SIX', SUBSTR(c.post_Cd,1,2))
			             ELSE '기타'
			        END                        AS area_nm,
			        f_april_gift_nm(d.GIFT_CD) AS gift_nm,
			        COUNT(*)                   AS give_cnt
			   FROM EVT_APRILDAY_GIFT_GIVE b,
			        member_info c,
			        evt_aprilday_win d
			 WHERE b.MEMBER_NO = c.MEMBER_NO(+)
			   AND b.coupon_no = d.coupon_no(+)
			   GROUP BY b.GIFT_GIVE_DTM, c.sex,c.birth_yyyy, c.post_Cd, d.GIFT_CD 
			   ORDER BY b.GIFT_GIVE_DTM, c.sex,c.birth_yyyy, c.post_Cd, d.GIFT_CD
			   ) T2
WHERE T1.event_dt = T2.event_dt(+)
AND   T1.sex      = T2.sex(+)
AND   T1.age      = T2.age(+)
AND   T1.area_nm  = T2.area_nm(+)
AND   T1.gift_nm  = T2.gift_nm(+);

배치돌릴라고하는데   T1.event_dt, T1.sex, T1.age, T1.area_nm, T1.gift_nm, 요거다섯개가 묶여서 PK인데 결과값이 잘안나오네요 ㅜ

53살남자 서울 아리따움.... 중복으로나와서요 .ㅡㅡㅡ
2결과값이 하나로나오구  T1.evt_win_cnt, T2.give_cnt 값이 SUM으로나오길바래요 ㅠㅠ
20180401	남자	53	서울	아리따움 뽀오얀미소발효립&아이	2	1
20180401	남자	53	서울	아리따움 뽀오얀미소발효립&아이	1	1
20180401	남자	21	서울	아리따움 뽀오얀미소발효립&아이	1	1

 

by 마농 [2018.03.13 08:35:13]

상품 당첨자와 수령자가 다를 수 있나요?
두 테이블의 키는 뭔가요? 쿠폰번호?
evt_aprilday_win / evt_aprilday_gift_give


by 우리집아찌 [2018.03.13 09:50:59]

인라인뷰안의 ORDER BY 는 필요 없을듯..


by 마농 [2018.03.13 10:17:54]
SELECT event_dt
     , sex
     , age
     , NVL2(post1, f_area_nm(post1, post2), '기타')  AS area_nm
     , f_april_gift_nm(gift_cd)                      AS gift_nm
     , NVL(SUM(evt_win_cnt), 0)                      AS evt_win_cnt
     , NVL(SUM(give_cnt   ), 0)                      AS give_cnt
  FROM (/* 만우절 경품 당첨자수 */
        SELECT a.event_dt
             , DECODE(b.sex, 'M', '남자', 'W', '여자', '기타')      AS sex
             , TO_CHAR(sysdate, 'yyyy') - b.birth_yyyy + 1          AS age
             , DECODE(LENGTH(TRIM(b.post_cd)), 5, 'FIVE', 6, 'SIX') AS post1
             , SUBSTR(b.post_cd, 1, 2)                              AS post2
             , a.gift_cd
             , COUNT(*) AS evt_win_cnt
             , 0        AS give_cnt
          FROM evt_aprilday_win       a
             , member_info            b
         WHERE a.member_no = b.member_no
         GROUP BY a.event_dt
             , b.sex
             , b.birth_yyyy
             , DECODE(LENGTH(TRIM(b.post_cd)), 5, 'FIVE', 6, 'SIX')
             , SUBSTR(b.post_cd, 1, 2)
             , a.gift_cd
         UNION ALL
        /* 만우절 경품 수령자수 */
        SELECT TO_CHAR(c.gift_give_dtm, 'yyyymmdd')                 AS event_dt
             , DECODE(b.sex, 'M', '남자', 'W', '여자', '기타')      AS sex
             , TO_CHAR(sysdate, 'yyyy') - b.birth_yyyy + 1          AS age
             , DECODE(LENGTH(TRIM(b.post_cd)), 5, 'FIVE', 6, 'SIX') AS post1
             , SUBSTR(b.post_cd, 1, 2)                              AS post2
             , a.gift_cd
             , 0        AS evt_win_cnt
             , COUNT(*) AS give_cnt
          FROM evt_aprilday_win       a
             , member_info            b
             , evt_aprilday_gift_give c
         WHERE c.member_no = b.member_no
           AND c.coupon_no = a.coupon_no
         GROUP BY TO_CHAR(c.gift_give_dtm, 'yyyymmdd')
             , b.sex
             , b.birth_yyyy
             , DECODE(LENGTH(TRIM(b.post_cd)), 5, 'FIVE', 6, 'SIX')
             , SUBSTR(b.post_cd, 1, 2)
             , a.gift_cd
        )
 GROUP BY event_dt, sex, age
     , NVL2(post1, f_area_nm(post1, post2), '기타')
     , f_april_gift_nm(gift_cd)
 ORDER BY event_dt, sex, age, area_nm, gift_nm
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입