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
mysql 累计值计算
标签:sum date having mem user span order by with rank
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源:http://www.cnblogs.com/kevinlucky/p/6871820.html