求累加sql写法

Question

某表t有字段a为
1
2
3
4
5
6
7生成出
1
3  (1+2)
6  (1+2+3)
10  ....
15
16
23

Answer 1

mysql> select (select sum(id) from t a where a.Id<=b.id) from t b;
+--------------------------------------------+
| (select sum(id) from t a where a.Id<=b.id) |
+--------------------------------------------+
|                                          1 |
|                                          3 |
|                                          6 |
|                                         10 |
|                                         15 |
|                                         21 |
|                                         28 |
+--------------------------------------------+
7 rows in set (0.02 sec)

Answer 2

如果没有唯一键的话
SET @i=0;
SET @j=0;
SELECT aa.id1,SUM(bb.id1)
FROM (
SELECT id1,@i:=@I+1 AS py FROM t) aa 
LEFT JOIN
(
SELECT id1,@j:=@j+1 AS py FROM t) bb
ON aa.py>=bb.pyGROUP BY aa.id1
16
23->
21
28 ?

Answer 3

假设ID为唯一：
SELECT aa.id1,SUM(bb.id1) FROM t aa LEFT JOIN t bb ON aa.id1>=bb.id1 GROUP BY aa.id1

Answer 4

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
7 rows in set (0.00 sec)mysql> select sum(b.a)
    -> from t a, t b
    -> where a.a>=b.a
    -> group by a.a;
+----------+
| sum(b.a) |
+----------+
|        1 |
|        3 |
|        6 |
|       10 |
|       15 |
|       21 |
|       28 |
+----------+
7 rows in set (0.03 sec)mysql>