mysql 累积求和

有如下表

id	money
1	10
2	20
3	30
4	40
5	50

求出按照id排序后，money的累加和sum

id	money	sum
1	10	10
2	20	30
3	30	60
4	40	100
5	50	150

 

建表语句：

create table sum_demo(id INT,money INT,primary key(id));

插入数据:

insert into sum_demo(id,money) values (1,10),(2,20),(3,30),(4,40),(5,50);

累积加和计算：

1.通过join on联合查询

select a.id,a.money,sum(b.money) as sum from sum_demo a
 join sum_demo b on a.id >= b.id group by a.id order by a.id;

2.通过设置局部变量查询

set @sum := 0;
select id,money,(@sum := @sum + money) as sum from sum_demo;

显示结果：

id	money	sum
1	10	10
2	20	30
3	30	60
4	40	100
5	50	150