SQL : 이벤트 대상자 목록, 결제정보 조회

Edi

문제발생 : 이벤트 대상 목록과 대상자 결제정보 필요

• 이벤트 대상자 조건 (하위 조건 모두 충족해야 한다)
  
  • 9월 자동결제 처음 이용한 고객
  • 10월 자동결제 이용한 고객
• 이벤트 대상자 필요 정보
  
  • 고객 결제정보

문제상황 : 이벤트 대상자 결제정보가 없다

• “결제_결과” 테이블을 사용하여 이벤트 대상자를 조회한다

SELECT NAME, PHONE, EMAIL, ... FROM '결제_결과'
WHERE PAYDATE IN (
SELECT PAYDATE FROM '결제_결과'
WHERE PAYDATE LIKE '201810%' AND PAYRESULT = 'Y')
AND PHONE IN (
SELECT PHONE FROM '결제_결과'
WHERE PAYDATE BETWEEN '20180901' AND '20180931' AND PAYRESULT = 'Y')
AND PHONE NOT IN (
SELECT PHONE FROM '결제_결과'
WHERE PAYDATE BETWEEN '20010101' AND '20180830' AND PAYRESULT = 'Y')
AND PAYRESULT = 'Y'


• IN을 사용하여 원하는 날짜에 결제한 고객을 찾고, NOT IN을 사용하여 원하지 않는 날짜에 결제한 고객은 제외한다.
• 정리하면
  
  • 10월에 결제한 고객을 IN (포함한다)
  • 9월에 결제한 고객을 IN (포함한다)
  • 9월, 10월을 제외한 다른 날짜에 결제한 했던 기록이 있다면 NOT IN (제외한다)
• 현재까지 이벤트 대상자가 조회되도록 작성되어 있다.
• 단, 이벤트 대상자의 결제정보는 조회할 수 없다.
• 이벤트 대상자 목록과 동시에 해당 고객의 결제정보를 조회할 방법이 필요하다.

문제해결 : INNER JOIN 사용하여 특정 테이블에서 원하는 정보 조회

• INNER JOIN을 사용하여 “결제_내역” 테이블에 있는 이벤트 대상자의 결제정보를 조회한다

SELECT res.NAME, res.PHONE, res.EMAIL, ... rec. PRODUCT_NAME, rec_PRODUCT_PRICE FROM '결제_결과' res
INNER JOIN '결제_내역' rec ON (res.SOME_CODE = rec.SOME_CODE)
WHERE rec.PRODUCT_NAME LIKE '%(자동결제)%'
AND rec.R_PAYDATE LIKE '201809%' OR rec.R_PAYDATE LIKE '201810%'
AND res.PAYDATE IN (
SELECT PAYDATE FROM '결제_결과'
WHERE PAYDATE LIKE '201810%' AND PAYRESULT = 'Y')
AND res.PHONE IN (
SELECT PHONE FROM '결제_결과'
WHERE PAYDATE BETWEEN '20180901' AND '20180931' AND PAYRESULT = 'Y')
AND res.PHONE NOT IN (
SELECT PHONE FROM '결제_결과'
WHERE PAYDATE BETWEEN '20010101' AND '20180830' AND PAYRESULT = 'Y')
AND res.PAYRESULT = 'Y'

• INNER JOIN을 사용하여 “결제_내역” 테이블에 있는 데이터를 조회할 수 있게 되었다.
  
  • 이벤트 기획자가 필요한 이벤트 대상자 결제정보를 조회결과를 통해 확인할 수 있다.

%23%20SQL%20%3A%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%20%uBAA9%uB85D%2C%20%uACB0%uC81C%uC815%uBCF4%20%uC870%uD68C%20%23%0A%0A@%28%5B%uBE14%uB85C%uADF8%5D%29%5B%uD2F0%uC2A4%uD1A0%uB9AC%5D%0A%0A%23%23%23%20%uBB38%uC81C%uBC1C%uC0DD%20%3A%20%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%20%uBAA9%uB85D%uACFC%20%uB300%uC0C1%uC790%20%uACB0%uC81C%uC815%uBCF4%20%uD544%uC694%20%23%23%23%0A-%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%20%uC870%uAC74%20%28%uD558%uC704%20%uC870%uAC74%20%uBAA8%uB450%20%uCDA9%uC871%uD574%uC57C%20%uD55C%uB2E4%29%0A%09-%209%uC6D4%20%uC790%uB3D9%uACB0%uC81C%20%uCC98%uC74C%20%uC774%uC6A9%uD55C%20%uACE0%uAC1D%0A%09-%2010%uC6D4%20%uC790%uB3D9%uACB0%uC81C%20%uC774%uC6A9%uD55C%20%uACE0%uAC1D%0A-%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%20%uD544%uC694%20%uC815%uBCF4%0A%09-%20%uACE0%uAC1D%20%uACB0%uC81C%uC815%uBCF4%0A%0A%0A%23%23%23%20%uBB38%uC81C%uC0C1%uD669%20%3A%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%20%uACB0%uC81C%uC815%uBCF4%uAC00%20%uC5C6%uB2E4%20%23%23%23%0A-%20%22%uACB0%uC81C_%uACB0%uACFC%22%20%uD14C%uC774%uBE14%uC744%20%uC0AC%uC6A9%uD558%uC5EC%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%uB97C%20%uC870%uD68C%uD55C%uB2E4%0A%3E%20%60%60%60%20SELECT%20NAME%2C%20PHONE%2C%20EMAIL%2C%20...%20%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%0AWHERE%20PAYDATE%20IN%20%28%0ASELECT%20PAYDATE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%20%0AWHERE%20PAYDATE%20LIKE%20%27201810%25%27%20AND%20PAYRESULT%20%3D%20%27Y%27%29%20%0AAND%20PHONE%20IN%20%28%0ASELECT%20PHONE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%0AWHERE%20PAYDATE%20BETWEEN%20%2720180901%27%20AND%20%2720180931%27%20AND%20PAYRESULT%20%3D%20%27Y%27%29%0AAND%20PHONE%20NOT%20IN%20%28%0ASELECT%20PHONE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%0AWHERE%20PAYDATE%20BETWEEN%20%2720010101%27%20AND%20%2720180830%27%20AND%20PAYRESULT%20%3D%20%27Y%27%29%20%0AAND%20PAYRESULT%20%3D%20%27Y%27%0A%60%60%60%0A-%20IN%uC744%20%uC0AC%uC6A9%uD558%uC5EC%20%uC6D0%uD558%uB294%20%uB0A0%uC9DC%uC5D0%20%uACB0%uC81C%uD55C%20%uACE0%uAC1D%uC744%20%uCC3E%uACE0%2C%20NOT%20IN%uC744%20%uC0AC%uC6A9%uD558%uC5EC%20%uC6D0%uD558%uC9C0%20%uC54A%uB294%20%uB0A0%uC9DC%uC5D0%20%uACB0%uC81C%uD55C%20%uACE0%uAC1D%uC740%20%uC81C%uC678%uD55C%uB2E4.%0A-%20%uC815%uB9AC%uD558%uBA74%0A%09-%2010%uC6D4%uC5D0%20%uACB0%uC81C%uD55C%20%uACE0%uAC1D%uC744%20IN%20%28%uD3EC%uD568%uD55C%uB2E4%29%0A%09-%209%uC6D4%uC5D0%20%uACB0%uC81C%uD55C%20%uACE0%uAC1D%uC744%20IN%20%28%uD3EC%uD568%uD55C%uB2E4%29%0A%09-%209%uC6D4%2C%2010%uC6D4%uC744%20%uC81C%uC678%uD55C%20%uB2E4%uB978%20%uB0A0%uC9DC%uC5D0%20%uACB0%uC81C%uD55C%20%uD588%uB358%20%uAE30%uB85D%uC774%20%uC788%uB2E4%uBA74%20NOT%20IN%20%28%uC81C%uC678%uD55C%uB2E4%29%0A-%20%uD604%uC7AC%uAE4C%uC9C0%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%uAC00%20%uC870%uD68C%uB418%uB3C4%uB85D%20%uC791%uC131%uB418%uC5B4%20%uC788%uB2E4.%0A-%20%uB2E8%2C%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%uC758%20%uACB0%uC81C%uC815%uBCF4%uB294%20%uC870%uD68C%uD560%20%uC218%20%uC5C6%uB2E4.%0A-%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%20%uBAA9%uB85D%uACFC%20%uB3D9%uC2DC%uC5D0%20%uD574%uB2F9%20%uACE0%uAC1D%uC758%20%uACB0%uC81C%uC815%uBCF4%uB97C%20%uC870%uD68C%uD560%20%uBC29%uBC95%uC774%20%uD544%uC694%uD558%uB2E4.%0A%0A%0A%23%23%23%20%uBB38%uC81C%uD574%uACB0%20%3A%20INNER%20JOIN%20%uC0AC%uC6A9%uD558%uC5EC%20%uD2B9%uC815%20%uD14C%uC774%uBE14%uC5D0%uC11C%20%uC6D0%uD558%uB294%20%uC815%uBCF4%20%uC870%uD68C%20%23%23%23%0A-%20INNER%20JOIN%uC744%20%uC0AC%uC6A9%uD558%uC5EC%20%22%uACB0%uC81C_%uB0B4%uC5ED%22%20%uD14C%uC774%uBE14%uC5D0%20%uC788%uB294%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%uC758%20%uACB0%uC81C%uC815%uBCF4%uB97C%20%uC870%uD68C%uD55C%uB2E4%0A%3E%20%60%60%60%20SELECT%20res.NAME%2C%20res.PHONE%2C%20res.EMAIL%2C%20...%20rec.%20PRODUCT_NAME%2C%20rec_PRODUCT_PRICE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%20res%0AINNER%20JOIN%20%27%uACB0%uC81C_%uB0B4%uC5ED%27%20rec%20ON%20%28res.SOME_CODE%20%3D%20rec.SOME_CODE%29%0AWHERE%20rec.PRODUCT_NAME%20LIKE%20%27%25%28%uC790%uB3D9%uACB0%uC81C%29%25%27%20%0AAND%20rec.R_PAYDATE%20LIKE%20%27201809%25%27%20OR%20rec.R_PAYDATE%20LIKE%20%27201810%25%27%0AAND%20res.PAYDATE%20IN%20%28%0ASELECT%20PAYDATE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%0AWHERE%20PAYDATE%20LIKE%20%27201810%25%27%20AND%20PAYRESULT%20%3D%20%27Y%27%29%0AAND%20res.PHONE%20IN%20%28%0ASELECT%20PHONE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%0AWHERE%20PAYDATE%20BETWEEN%20%2720180901%27%20AND%20%2720180931%27%20AND%20PAYRESULT%20%3D%20%27Y%27%29%0AAND%20res.PHONE%20NOT%20IN%20%28%0ASELECT%20PHONE%20FROM%20%27%uACB0%uC81C_%uACB0%uACFC%27%0AWHERE%20PAYDATE%20BETWEEN%20%2720010101%27%20AND%20%2720180830%27%20AND%20PAYRESULT%20%3D%20%27Y%27%29%0AAND%20res.PAYRESULT%20%3D%20%27Y%27%20%60%60%60%0A-%20INNER%20JOIN%uC744%20%uC0AC%uC6A9%uD558%uC5EC%20%22%uACB0%uC81C_%uB0B4%uC5ED%22%20%uD14C%uC774%uBE14%uC5D0%20%uC788%uB294%20%uB370%uC774%uD130%uB97C%20%uC870%uD68C%uD560%20%uC218%20%uC788%uAC8C%20%uB418%uC5C8%uB2E4.%0A%09-%20%uC774%uBCA4%uD2B8%20%uAE30%uD68D%uC790%uAC00%20%uD544%uC694%uD55C%20%uC774%uBCA4%uD2B8%20%uB300%uC0C1%uC790%20%uACB0%uC81C%uC815%uBCF4%uB97C%20%uC870%uD68C%uACB0%uACFC%uB97C%20%uD1B5%uD574%20%uD655%uC778%uD560%20%uC218%20%uC788%uB2E4.