mysql求累计报表问题

在报表问题中，累计报表是最基础、最普遍的一部分，最近有些朋友一直在询问。

现写下简单的例子，供初学者学习使用。

累计报表问题：
求每人每月的总收入，及其累计(即本月及其以前月份的累计收入)收入；

数据：
username,month,salary
A,2018/01,5
A,2018/01,15
B,2018/01,5.2
A,2018/01,8.7
B,2018/01,25
A,2018/01,5.7
C,2018/01,10.4
C,2018/01,20
A,2018/02,4
A,2018/02,6
C,2018/02,3.0
C,2018/02,11.6
B,2018/02,10.7
B,2018/02,5
A,2018/03,14
A,2018/03,6.5
B,2018/03,20.9
B,2018/03,25
C,2018/03,10
C,2018/03,20
A,2018/04,14
A,2018/04,6.5
B,2018/04,20.9
B,2018/04,25
C,2018/04,10
C,2018/04,20
A,2018/05,14
A,2018/05,6.5
B,2018/05,20.9
B,2018/05,23.5
C,2018/05,10
C,2018/05,20
A,2018/06,14
A,2018/06,6.5
B,2018/06,20.9
B,2018/06,2.5
C,2018/06,10.98
C,2018/06,20.45

建表：
drop table if exists t_user;
create table t_user(
    `id` int NOT NULL AUTO_INCREMENT,
    `username` varchar(16) NOT NULL DEFAULT '0',
    `month` varchar(16) NOT NULL DEFAULT '0',
    `salary` double(12,6) NOT NULL DEFAULT 0.000000,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

导入数据：

insert into t_user(username,month,salary) values
('A','2018/01',5),
('A','2018/01',15),
('B','2018/01',5.2),
('A','2018/01',8.7),
('B','2018/01',25),
('A','2018/01',5.7),
('C','2018/01',10.4),
('C','2018/01',20),
('A','2018/02',4),
('A','2018/02',6),
('C','2018/02',3.0),
('C','2018/02',11.6),
('B','2018/02',10.7),
('B','2018/02',5),
('A','2018/03',14),
('A','2018/03',6.5),
('B','2018/03',20.9),
('B','2018/03',25),
('C','2018/03',10),
('C','2018/03',20),
('A','2018/04',14),
('A','2018/04',6.5),
('B','2018/04',20.9),
('B','2018/04',25),
('C','2018/04',10),
('C','2018/04',20),
('A','2018/05',14),
('A','2018/05',6.5),
('B','2018/05',20.9),
('B','2018/05',23.5),
('C','2018/05',10),
('C','2018/05',20),
('A','2018/06',14),
('A','2018/06',6.5),
('B','2018/06',20.9),
('B','2018/06',2.5),
('C','2018/06',10.98),
('C','2018/06',20.45);

答案：
----------------------------------------------------------------------------------------------
select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from 
(select username,month,sum(salary) as salary from t_user group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_user group by username,month) B
on
A.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;
-----------------------------------------------------------------------------------------------