sql 按店铺日期分月累计统计

Question

求一个SQL写法，求出每个店铺的分月累计值，比如：
6月累计销售额只统计6月的累计，7月累计销售额只统计7月的累计。
样式如下图：

跪求大神恩赐，点拨。

Answer 1

`题主的最后一列数据应该是想用SQL计算出来的

--假定前三列表结构如下
CREATE TABLE
    DSV.TABLE_TEST       --定义模式名&表名
(
DIANPU VARCHAR(10),
XIAOSHOUE DECIMAL(31,4),
RIQI DATE
) 
COMMENT ON TABLE "DSV"."TABLE_TEST" IS '测试表';  --表注释
COMMENT ON COLUMN DSV.TABLE_TEST.DIANPU IS '店铺';--字段注释
COMMENT ON COLUMN DSV.TABLE_TEST.XIAOSHOUE IS '销售额';
COMMENT ON COLUMN DSV.TABLE_TEST.RIQI IS '日期';
--插入数据
insert into DSV.TABLE_TEST  values ('A',100,'2020-06-01');
insert into DSV.TABLE_TEST  values ('A',200,'2020-06-02');
insert into DSV.TABLE_TEST  values ('A',30,'2020-06-03');
insert into DSV.TABLE_TEST  values ('A',40,'2020-07-01');
insert into DSV.TABLE_TEST  values ('A',5,'2020-07-02');
insert into DSV.TABLE_TEST  values ('A',50,'2020-07-03');
insert into DSV.TABLE_TEST  values ('B',30,'2020-06-01');
insert into DSV.TABLE_TEST  values ('B',500,'2020-06-02');
insert into DSV.TABLE_TEST  values ('B',50,'2020-06-03');
insert into DSV.TABLE_TEST  values ('B',100,'2020-07-01');
insert into DSV.TABLE_TEST  values ('B',20,'2020-07-02');
insert into DSV.TABLE_TEST  values ('B',100,'2020-07-03');
insert into DSV.TABLE_TEST  values ('C',10,'2020-06-01');
insert into DSV.TABLE_TEST  values ('C',20,'2020-06-02');
insert into DSV.TABLE_TEST  values ('C',10,'2020-06-03');
insert into DSV.TABLE_TEST  values ('C',30,'2020-07-01');
insert into DSV.TABLE_TEST  values ('C',50,'2020-07-02');
insert into DSV.TABLE_TEST  values ('C',20,'2020-07-03');

原表结构如下

--可使用如下代码实现每天的月累计计算.
select 
A.DIANPU,
A.XIAOSHOUE,
A.RIQI,
sum(b.XIAOSHOUE) AS LEIJIXIAOSHOUE
from 
DSV.TABLE_TEST A join DSV.TABLE_TEST B on A.DIANPU=b.DIANPU and MONTH(A.RIQI)=MONTH(b.RIQI) and A.RIQI>=b.RIQI 
group by 
A.DIANPU,
A.XIAOSHOUE,
A.RIQI
order by
A.DIANPU,
A.RIQI

结果如下：