原始数据:
先要的结果:
思路:
原始表中只有日期,金额字段
1.首先确定出日期所在的月份(DATE_FORMAT(date, ‘%Y-%m-01’))
2.表自关联
3.条件:相同月份,小于当前日期的所有值
select a.f1, a.f2, a.f3, sum(b.f6)
from (select DATE_FORMAT(date, '%Y-%m-01') f1, DATE_FORMAT(date, '%Y-%m-%d') f2, sum(money) as f3
from order_table
where date is not null
group by f1,f2) a
join (select DATE_FORMAT(date, '%Y-%m-01') f4, DATE_FORMAT(date, '%Y-%m-%d') f5, sum(money) f6
from order_table
where date is not null
group by f4,f5) b on (a.f1 = b.f4 and a.f2 >= b.f5)
group by a.f1, a.f2, a.f3