데이터베이스 질문과 답변
도와주세요 ㅠ
0
3
624
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.