有一张表
year month num cancelnum
2009 11 25 3
2009 12 45 5
2010 03 20 10
2010 07 80 100
2011 05 20 5如何写SQL能计算num和cancelnum的累加值
year month num cancelnum
2009 11 25 3
2009 12 70 8
2010 03 90 18
2010 07 170 118
2011 05 190 123谢谢
year month num cancelnum
2009 11 25 3
2009 12 45 5
2010 03 20 10
2010 07 80 100
2011 05 20 5如何写SQL能计算num和cancelnum的累加值
year month num cancelnum
2009 11 25 3
2009 12 70 8
2010 03 90 18
2010 07 170 118
2011 05 190 123谢谢
期望的表我贴出来了 是每一行的累加 不是简单的sum
union
SELECT sum(num),sum(cancelnum) from FROM 表名 where year=2009
union
SELECT sum(num),sum(cancelnum) from FROM (SELECT sum(num),sum(cancelnum) from FROM 表名 where year=2009 )
union
。。
( Select SUM(num)
From 表
Where year <= A.year And month <= A.month
) as snum
From (
Select year, month
From 表
Order By year, month
) A
或者用存储过程来做。
sum(num) over(order by id) as num,
sum(cancelnum) over(order by id) as cancelnum
from T;
--mysql
create table test(year int,month int,num int,cancelnum int);
insert into test values(2009,11,25,3);
insert into test values(2009,12,45,5);
insert into test values(2010,03,20,10);
insert into test values(2010,07,80,100);
insert into test values(2011,05,20,5);set @rownum=0;
create table test2 select @rownum:=@rownum+1 as rownum,year,month,num,cancelnum from test; mysql> select * from test2;
+--------+------+-------+------+-----------+
| rownum | year | month | num | cancelnum |
+--------+------+-------+------+-----------+
| 1 | 2009 | 11 | 25 | 3 |
| 2 | 2009 | 12 | 45 | 5 |
| 3 | 2010 | 3 | 20 | 10 |
| 4 | 2010 | 7 | 80 | 100 |
| 5 | 2011 | 5 | 20 | 5 |
+--------+------+-------+------+-----------+mysql> select a.rownum,a.year,a.month,sum(b.num),sum(b.cancelnum) from test2 as a, test2 as b where b.rownum<=a.rownum group by a.rownum,a.year,a.month;
+--------+------+-------+------------+------------------+
| rownum | year | month | sum(b.num) | sum(b.cancelnum) |
+--------+------+-------+------------+------------------+
| 1 | 2009 | 11 | 25 | 3 |
| 2 | 2009 | 12 | 70 | 8 |
| 3 | 2010 | 3 | 90 | 18 |
| 4 | 2010 | 7 | 170 | 118 |
| 5 | 2011 | 5 | 190 | 123 |
+--------+------+-------+------------+------------------+
我一直就是错在这句了year <= A.year And month <= A.month
应该是(month <= A.month and year =A.year) or (year < A.year)
大意了。。