데이터베이스 질문과 답변
안녕하세요.
이번에 처음으로 정산 쪽 업무를 맡게 되었는데 돈과 관련된 부분이라 신중을 기해야되서 제가 작성한 SQL이 정확한건지 문의 드립니다.
결제 테이블
| MG_PAY |
| MG_CD(PK) |
| MG_PAY_NO(PK) |
| OR_PAY_AMT |
| OR_PAY_FEE |
입금 테이블
| MG_DPS |
| MG_CD(PK) |
| MG_PAY_NO(PK) |
| PG_PAY_AMT |
| PG_PAY_FEE |
결제, 입금 2개의 테이블이 있으며 동일한 PK값을 가지고 있습니다.
비교 컬럼은 OR_PAY_AMT, PG_PAY_AMT 금액과 OR_PAY_FEE, PG_PAY_FEE 수수료 인데
아래는 결제 테이블과 입금 테이블을 비교하여 금액과 수수료가 동일하지 않은 것들만 추려내기 위해 작성한 SQL 입니다.
SELECT A.MG_CD AS mgCd
,A.MG_PAY_NO AS mgPayNo
,B.OR_PAY_AMT AS orPayAmt
,B.OR_PAY_FEE AS orPayFee
,A.PAY_AMT AS pgPayAmt
,A.PAY_FEE AS pgPayFee
FROM (
SELECT
MG_CD, MG_PAY_NO, OR_PAY_AMT AS PAY_AMT, OR_PAY_FEE AS PAY_FEE
FROM MG_PAY
MINUS
SELECT
MG_CD, MG_PAY_NO, PG_PAY_AM AS PAY_AMT, PG_PAY_FEE AS PAY_FEE
FROM MG_DPS
UNION
SELECT
MG_CD, MG_PAY_NO, PG_PAY_AMT AS PAY_AMT, PG_PAY_FEE AS PAY_FEE
FROM MG_DPS
MINUS
SELECT
MG_CD, MG_PAY_NO, OR_PAY_AMT AS PAY_AMT, OR_PAY_FEE AS PAY_FEE
FROM MG_PAY
) A,
MG_PAY B
WHERE A.MG_CD = B.MG_CD(+)
AND A.MG_PAY_NO = B.MG_PAY_NO(+)
아직 실력과 경험이 부족해서 염치 불고 하고 올려봅니다.
정산쪽에서 보통 금액 비교할때 작성하는 SQL 방법이나 제가 작성한 SQL문 에서 잘못 작성된 부분 좀 짚어주세요.
by 야신
[2017.03.28 18:08:17]
이렇게 해보시면 될것 같습니다.
SELECT nvl(a.mg_cd, b.mg_cd) AS mg_cd
,nvl(a.mg_pay_no, b.mg_pay_no) AS mg_pay_no
,or_pay_amt
,or_pay_fee
,pg_pay_amt
,pg_pay_fee
,nvl(or_pay_amt, 0) - nvl(pg_pay_amt, 0) amt_diff
,nvl(or_pay_fee, 0) - nvl(pg_pay_fee, 0) fee_diff
FROM mg_pay a
FULL OUTER JOIN mg_dps b
ON a.mg_cd = b.mg_cd
AND a.mg_pay_no = b.mg_pay_no
WHERE (nvl(or_pay_amt, 0) <> nvl(pg_pay_amt, 0))
OR (nvl(or_pay_fee, 0) <> nvl(pg_pay_fee, 0))
결제와 입금 어느쪽에만 있을 한쪽에만 있을 경우를 생각해서 FULL OUTER JOIN 이나
UNION ALL 을 이용한 비교를 해야 할것 같습니다.
일단 SQL 이 약한것 같으니 먼저 강좌를 쭉 따라해 보시기를 권합니다.
by 마농
[2017.03.29 07:46:09]
사용하신 방법도 가능한 방법이긴 합니다.
- 다만 비효율적이며, 사용법도 틀렸습니다.
- UNION 이 아닌 UNION ALL 해야 하며
- UNION ALL 이 맨 마지막에 수행되어야 하는데 그렇지 않습니다.
- MINUS 를 괄호로 묶어서 UNION ALL 이 맨 마지막에 수행되도록 해야 합니다.
SELECT mg_cd
, mg_pay_no
, SUM(or_pay_amt) or_pay_amt
, SUM(or_pay_fee) or_pay_fee
, SUM(pg_pay_amt) pg_pay_amt
, SUM(pg_pay_fee) pg_pay_fee
FROM (SELECT mg_cd
, mg_pay_no
, or_pay_amt
, or_pay_fee
, 0 pg_pay_amt
, 0 pg_pay_fee
FROM mg_pay
UNION ALL
SELECT mg_cd
, mg_pay_no
, 0 or_pay_amt
, 0 or_pay_fee
, pg_pay_amt
, pg_pay_fee
FROM mg_dps
)
GROUP BY mg_cd, mg_pay_no
HAVING SUM(or_pay_amt) != SUM(pg_pay_amt)
OR SUM(or_pay_fee) != SUM(pg_pay_fee)
;
) 버튼을 클릭하여 작성 하시면 됩니다.