SELECT ROW_NUMBER() OVER(ORDER BY amtm.usemon) as rn,
amtm.usemon,
amt.movienm,
amcc.baseamt,
sum(amtm.cnt) over(partition by amt.moviecd) as cnt,
amcc.divrate,
SUM(amtm.amt)OVER(PARTITION BY amt.moviecd) as totamt,
baseamt*(cnt*(divrate/100)) as settamt,
SUM(amtm.cnt)over(partition by amtm.usemon) as mtcnt,
SUM(amtm.amt)over(partition by amtm.usemon) as mtamt
FROM aibill.aimovtitlemonth amtm,
aibill.aimovcalc amcc,
aibill.aimovtitle amt
WHERE amtm.moviecd = amcc.moviecd
AND amtm.moviecd = amt.moviecd
AND amcc.calccd = 'C0001'
AND amtm.usemon = '200812'
AND amt.coopcd = 'AI000001'
위 쿼리 결과가
RN USEMON MOVIENM BASEAMT CNT DIVRATE TOTAMT SETTAMT MTCNT MTAMT
1 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
2 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
3 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
4 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
5 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
6 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
7 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
8 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
9 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
10 200812 걸스카우트 0 333 40 600000 0 28627 47044280
11 200812 걸스카우트 0 333 40 600000 0 28627 47044280
이런식으로 movienm에 중복데이터가 나오는데요 데이블중 aimovtitlemonth부분에 coopcd가 틀려서 coopcd갯수만큼 중복데이터가 나와요.
.제가원하는결과는 중복제거로
1 200812 용의주도미스신 0 263 40 502000 0 28627 47044280
2 200812 걸스카우트 0 333 40 600000 0 28627 47044280
이렇게 결과가 나오게 하고싶은데요. 어떻게 해야하는지 알려주세요.
|
