mysql 累计值_mysql 累计值计算

mysql 累计计算selectDateID TRAN_DT,

REGI_CH REGI_CH,

TRAN_CH TRAN_CH,

IFNULL(DRAW_CT,0) DRAW_CT,

round(IFNULL(DRAW_AMT,0),2) DRAW_AMT,

ALL_DRAW_CT,

round(ALL_DRAW_AMT,2) ALL_DRAW_AMTfrom (selectd.DateID,

d.REGI_CH REGI_CH,

d.TRAN_CH TRAN_CH,

DRAW_CT,

DRAW_AMT,

@rownum := @rownum + 1,

IF(@REGI_CH= d.REGI_CH and @TRAN_CH =d.TRAN_CH,

@ALL_DRAW_CT := @ALL_DRAW_CT + ifnull(b.DRAW_CT, 0),

@ALL_DRAW_CT :=b.DRAW_CT) AS ALL_DRAW_CT,

IF(@REGI_CH= d.REGI_CH and @TRAN_CH =d.TRAN_CH,

@ALL_DRAW_AMT := @ALL_DRAW_AMT + ifnull(b.DRAW_AMT, 0),

@ALL_DRAW_AMT :=b.DRAW_AMT) AS ALL_DRAW_AMT,

IF(@REGI_CH= d.REGI_CH and @TRAN_CH =d.TRAN_CH,

@rank := @rank + 1,

@rank := 1) AS rank,

@REGI_CH :=d.REGI_CH,

@TRAN_CH :=d.TRAN_CH

FROM (selectdateid, REGI_CH, TRAN_CHfrom (selectd.dateid, REGI_CH, TRAN_CH, TRAN_DTfrom (selectTRAN_DT,

REGI_CH,

TRAN_CH,

IF(@REGI_CH=b.REGI_CH and

@TRAN_CH=b.TRAN_CH,

@rank := @rank + 1,

@rank := 1) AS rank,

@REGI_CH :=b.REGI_CH,

@TRAN_CH :=b.TRAN_CH

FROM (selectTRANDT TRAN_DT,

u.REGI_CH,

TRANCH TRAN_CHfromods_bd_withdraw t

join ods_bd_user_person u

on u.member_cd=t.member_cdwhere trandt > ‘20150515‘and trandt< ‘${trandate}‘group by trandt, u.REGI_CH, TRANCH

order by u.REGI_CH, TRANCH, trandt) b,

(SELECT @REGI_CH :=NULL,

@TRAN_CH := null,

@rank := 0) c

having rank= 1) n

join dim_date d

on1 = 1

where d.dateid > ‘20150515‘and d.dateid< ‘${trandate}‘) fwhere f.TRAN_DT <=dateid

order by REGI_CH, TRAN_CH, dateid) d

left join (selectTRANDT TRAN_DT,

u.REGI_CH,

TRANCH TRAN_CH,

count(*) DRAW_CT,

sum(TRANAM) DRAW_AMTfromods_bd_withdraw t

join ods_bd_user_person u

on u.member_cd=t.member_cdwhere trandt > ‘20150515‘and trandt< ‘${trandate}‘group by trandt, u.REGI_CH, TRANCH

order by u.REGI_CH, TRANCH, trandt) b

on b.TRAN_DT=d.DateID

and d.REGI_CH=b.REGI_CH

and d.TRAN_CH=b.TRAN_CH

join (SELECT @rownum := 0,

@REGI_CH :=NULL,

@TRAN_CH := null,

@ALL_DRAW_CT := 0,

@ALL_DRAW_AMT := 0,

@rank := 0) c

on1 = 1order by dateid) t