mysql 累计值计算
时间：2021-07-01 10:21:17
帮助过：12人阅读
select DateID 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_AMT
from (
select d.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 (select dateid, REGI_CH, TRAN_CH
from (
select d.dateid, REGI_CH, TRAN_CH, TRAN_DT
from (
select TRAN_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 (select TRANDT TRAN_DT,
u.REGI_CH,
TRANCH TRAN_CH
from ods_bd_withdraw t
join ods_bd_user_person u
on u.member_cd =
t.member_cd
where 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
on 1 =
1
where d.dateid >
‘20150515‘
and d.dateid <
‘${trandate}‘ ) f
where f.TRAN_DT <=
dateid
order by REGI_CH, TRAN_CH, dateid) d
left join (select TRANDT TRAN_DT,
u.REGI_CH,
TRANCH TRAN_CH,
count(*
) DRAW_CT,
sum(TRANAM) DRAW_AMT
from ods_bd_withdraw t
join ods_bd_user_person u
on u.member_cd =
t.member_cd
where 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
on 1 =
1
order by dateid) t
mysql 累计值计算
标签：sum date having mem user span order by with rank
