表 月份 收入增加
1 36000
1 46000
3 36000
5 36000
6 36000
11 36000
9 36000
6 36000如何得到
月份 所有收入增加
1 36000
2 46000
3 36000
4 36000
6 36000
7 36000
8 36000
9 36000
10 53543
11 353000
12 345000select 月份,sum(所有收入增加)
FROM ..... gorup by 月份 order by 月份但是 该结果没有计算出 该月的实际增加值
即 1月无须动 2 月需要减去1月 3月需要减去2月 12月需要减去11月数值
如何?
1 36000
1 46000
3 36000
5 36000
6 36000
11 36000
9 36000
6 36000如何得到
月份 所有收入增加
1 36000
2 46000
3 36000
4 36000
6 36000
7 36000
8 36000
9 36000
10 53543
11 353000
12 345000select 月份,sum(所有收入增加)
FROM ..... gorup by 月份 order by 月份但是 该结果没有计算出 该月的实际增加值
即 1月无须动 2 月需要减去1月 3月需要减去2月 12月需要减去11月数值
如何?
(
c1 char(1),
c2 int
)insert test (c1,c2)
select 'a',2 union all
select 'b',2 union all
select 'c',5 union all
select 'd',9 select * from testselect c1,(select sum(c2) from test where c1<=a.c1) as c2 from test adrop table test
FROM 表 A
看可不可以啊, 我没测试
思路应当是对的,如果有问题自己可以修下
drop table tb
gocreate table tb([month] int , val int)
insert into tb([month] , val) values(1 , 10)
insert into tb([month] , val) values(2 , 20)
insert into tb([month] , val) values(3 , 30)
insert into tb([month] , val) values(4 , 40)
insert into tb([month] , val) values(5 , 50)
insert into tb([month] , val) values(6 , 60)
insert into tb([month] , val) values(7 , 70)
insert into tb([month] , val) values(8 , 80)
insert into tb([month] , val) values(9 , 90)
insert into tb([month] , val) values(10 , 100)
insert into tb([month] , val) values(11 , 110)
insert into tb([month] , val) values(12 , 120)
goselect [month] , val as 比上月增加值 from tb where [month] = 1
union all
select n.[month] , n.val - m.val as 比上月增加值 from
(select * from tb) m,
(select * from tb) n
where m.[month] = n.[month] - 1drop table tb/*
month 比上月增加值
----------- -----------
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10
11 10
12 10(所影响的行数为 12 行)*/
drop table tb
gocreate table tb([month] int , val int)
insert into tb([month] , val) values(1 , 10)
insert into tb([month] , val) values(1 , 10)
insert into tb([month] , val) values(2 , 20)
insert into tb([month] , val) values(3 , 30)
insert into tb([month] , val) values(4 , 40)
insert into tb([month] , val) values(4 , 40)
insert into tb([month] , val) values(5 , 50)
insert into tb([month] , val) values(6 , 60)
insert into tb([month] , val) values(7 , 70)
insert into tb([month] , val) values(7 , 70)
insert into tb([month] , val) values(8 , 80)
insert into tb([month] , val) values(9 , 90)
insert into tb([month] , val) values(10 , 100)
insert into tb([month] , val) values(11 , 110)
insert into tb([month] , val) values(12 , 120)
goselect [month] , sum(val) as 比上月增加值 from tb where [month] = 1 group by [month]
union all
select n.[month] , n.val - m.val as 比上月增加值 from
(select [month] , sum(val) val from tb group by [month]) m,
(select [month] , sum(val) val from tb group by [month]) n
where m.[month] = n.[month] - 1drop table tb/*
month 比上月增加值
----------- -----------
1 20
2 0
3 10
4 50
5 -30
6 10
7 80
8 -60
9 10
10 10
11 10
12 10(所影响的行数为 12 行)*/
insert into tb([month] , val) values(1 , 10)
insert into tb([month] , val) values(1 , 10)
insert into tb([month] , val) values(2 , 20)
insert into tb([month] , val) values(3 , 30)
insert into tb([month] , val) values(4 , 40)
insert into tb([month] , val) values(4 , 40)
insert into tb([month] , val) values(5 , 50)
insert into tb([month] , val) values(6 , 60)
insert into tb([month] , val) values(7 , 70)
insert into tb([month] , val) values(7 , 70)
insert into tb([month] , val) values(8 , 80)
insert into tb([month] , val) values(9 , 90)
insert into tb([month] , val) values(10 , 100)
insert into tb([month] , val) values(11 , 110)
insert into tb([month] , val) values(12 , 120)
goselect a.[month],isnull(a.val-b.val,a.val)比上月增加值 from tb a left join tb b on a.[month]=b.[month]+1