select identity(int,1,1) as id ,* into #t from 表select #t. 姓名,#t.增加金额,#t.增加金额余额 =(select sum(增加金额-增加金额) from #t where id<=a.id) from #t adrop table #t 試下上面的
试试这样: 可以通过增加一个主键来达到你的目的,另外可能是你的条件中写错了把应该是S4=S3+(a4-b4), 假设你的表名为Talter table T add id int identity(1,1) select T1.姓名,T1.增加金额,T1.减少金额, 余额=T2.余额+(T1.增加金额-T1.减少余额) from T T1 , (select id,姓名,增加金额,减少金额,(增加金额-减少余额) as 余额 from T) T2 where T1.id=T2.id+1
我最后的条件where T1.id=T2.id+1 中的加号是用了全角,注意用的时候改回半角加号
select id=identity(int),姓名,增加金额,减少金额,余额=增加金额,减少金额 into # from 源表 select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id) from # a drop table #
--zjcxc(邹建)的语句好像有点问题 --向下面这样应该就可以了select id=identity(int),姓名,增加金额,减少金额,余额=增加金额 into #tb1 from 源表select 姓名,增加金额,减少金额, 余额=(select 余额 from #tb1 where id=(select id=max(id) from tb1 where id<a.id))+增加金额-减少金额 from #tb1 adrop table #tb1
--搞错了,再改改select id=identity(int),姓名,增加金额,减少金额,余额=增加金额-减少金额 into #tb1 from 源表select 姓名,增加金额,减少金额,余额=(select sum(余额) from #tb1 where id<=a.id)) from #tb1 adrop table #tb1
如果 i的范围属于[0,n] , 假定 上题中 a[1],a[2],a[3]...a[n] 是按照顺序排列的也就是 b[n] ,s[n]也一样.那么 s[n] = s[n-1] + a[n] - b[n]展开 s[n] 可得 s[n] = sum(a[1..n]) -sum(b[1..n]) 也可以是 s[n] = sum(a[1..n] - b[1..n])a[1..n] 意思是 从1..n遍历取a[i]的值所以余额的表达式可以这么写select A.name ,A.series, A.increase , A.reduction , sum(B.increase - B.reduction) As balance from table_name A , table_name B where A.name = B.name and B.series >= A.series group by A.name ,A.series ,A.increase , A.reduction series是原表中没有的字段,我加进去的,用以定位a1,a2,a3,an的顺序的, 也就是说 series是同一个人的不同纪录的序号标志. 不然单靠"增加金额"一个域的是无法判断顺序的. 如果你的数据库支持在 select 列 引用查询 ,那么也可以这么做select A.name ,A.series, A.increase , A.reduction , (select sum(B.increase - B.reduction) from table_name B where B.name = A.name and B.series >= A.series ) As balance from table_name A
--减号误写成,了select id=identity(int),姓名,增加金额,减少金额,余额=增加金额-减少金额 into # from 源表 select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id) from # a drop table #
--还应该考虑姓名不相同时不能累加 select id=identity(int),姓名,增加金额,减少金额,余额=增加金额-减少金额 into # from 源表 select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id and 姓名=a.姓名) from # a drop table #
--测试数据 create table tb(姓名 varchar(10),增加金额 int,减少金额 int) insert tb select '张三',10,0 union all select '张三',12,10 union all select '张三',13,-20 union all select '李四',14,30 union all select '张三',15,40 union all select '李四',16,-90 union all select '张三',70,70 union all select '李四',80,08 union all select '李四',90,03 union all select '李四',10,-10 go--查询 select id=identity(int),姓名,增加金额,减少金额,余额=增加金额-减少金额 into # from tb select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id and 姓名=a.姓名) from # a drop table # go--删除测试 drop table tb /*--结果姓名 增加金额 减少金额 余额 ---------- ----------- ----------- ----------- 张三 10 0 10 张三 12 10 12 张三 13 -20 45 李四 14 30 -16 张三 15 40 20 李四 16 -90 90 张三 70 70 20 李四 80 8 162 李四 90 3 249 李四 10 -10 269(所影响的行数为 10 行) --*/
select identity(int,1,1) as id ,* into #t from 表select #t. 姓名,#t.增加金额,#t.增加金额余额 =(select sum(增加金额-增加金额) from #t where id<=a.id) from #t adrop table #t 試下上面的 沒留意出了點問題 select a. 姓名,a.增加金额,a.增加金额余额 =(select sum(增加金额-增加金额) from #t where id<=a.id) from #t a---這樣
/* create table test1 ( iid int identity(1,1), ss varchar(10), sname int, sname1 int, ) insert into test1 select '张三','23123','212' union all select '张三','112','123' union all select '张三','2323','212' union all select '张三','2313','212' union all select '张三','2313','212' union all select '张三','2123','212' union all select '张三','23123','212' */ --select * from test1 select a.* ,余额=(select sum(sname)-sum(sname1) from test1 b where b.iid<=a.iid) from test1 a
if not exists(select * from sysobjects where name='tb') begin create table tb ( [ID] int identity(1,1), [Name] varchar(50) default '', addmoney money default 0.00, submoney money default 0.00 ) end else begin truncate table tb endinsert tb([Name],addmoney,submoney) select '张三',100,86 union select '张三',200,34 union select '张三',400,356 union select '张三',67,24select A.*,(select sum(addmoney)-sum(submoney) from tb as B where B.ID<=A.ID) as [left] from tb as A
=(select sum(增加金额-增加金额) from #t where id<=a.id) from #t adrop table #t
試下上面的
可以通过增加一个主键来达到你的目的,另外可能是你的条件中写错了把应该是S4=S3+(a4-b4),
假设你的表名为Talter table T add id int identity(1,1)
select T1.姓名,T1.增加金额,T1.减少金额,
余额=T2.余额+(T1.增加金额-T1.减少余额)
from T T1 ,
(select id,姓名,增加金额,减少金额,(增加金额-减少余额) as 余额 from T) T2
where T1.id=T2.id+1
select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id) from # a
drop table #
其中 s1:=a1-b1; s2:=s1+(a2-b2); s3:=s2+(a3-b3); s4:=s3+(a4-b4).....
--向下面这样应该就可以了select id=identity(int),姓名,增加金额,减少金额,余额=增加金额 into #tb1 from 源表select 姓名,增加金额,减少金额,
余额=(select 余额 from #tb1 where id=(select id=max(id) from tb1 where id<a.id))+增加金额-减少金额
from #tb1 adrop table #tb1
如果 i的范围属于[0,n] ,
假定 上题中 a[1],a[2],a[3]...a[n] 是按照顺序排列的也就是 b[n] ,s[n]也一样.那么 s[n] = s[n-1] + a[n] - b[n]展开 s[n] 可得 s[n] = sum(a[1..n]) -sum(b[1..n])
也可以是 s[n] = sum(a[1..n] - b[1..n])a[1..n] 意思是 从1..n遍历取a[i]的值所以余额的表达式可以这么写select A.name ,A.series, A.increase , A.reduction , sum(B.increase - B.reduction) As balance
from table_name A , table_name B where A.name = B.name and B.series >= A.series
group by A.name ,A.series ,A.increase , A.reduction
series是原表中没有的字段,我加进去的,用以定位a1,a2,a3,an的顺序的,
也就是说 series是同一个人的不同纪录的序号标志. 不然单靠"增加金额"一个域的是无法判断顺序的.
如果你的数据库支持在 select 列 引用查询 ,那么也可以这么做select A.name ,A.series, A.increase , A.reduction ,
(select sum(B.increase - B.reduction) from table_name B where B.name = A.name and B.series >= A.series ) As balance
from table_name A
同时如果能找到定位 判断 a1,a2的顺序的方法也可以不用增加 series那个字段.
select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id) from # a
drop table #
select id=identity(int),姓名,增加金额,减少金额,余额=增加金额-减少金额 into # from 源表
select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id and 姓名=a.姓名) from # a
drop table #
create table tb(姓名 varchar(10),增加金额 int,减少金额 int)
insert tb select '张三',10,0
union all select '张三',12,10
union all select '张三',13,-20
union all select '李四',14,30
union all select '张三',15,40
union all select '李四',16,-90
union all select '张三',70,70
union all select '李四',80,08
union all select '李四',90,03
union all select '李四',10,-10
go--查询
select id=identity(int),姓名,增加金额,减少金额,余额=增加金额-减少金额 into # from tb
select 姓名,增加金额,减少金额,余额=(select sum(余额) from # where id<=a.id and 姓名=a.姓名) from # a
drop table #
go--删除测试
drop table tb
/*--结果姓名 增加金额 减少金额 余额
---------- ----------- ----------- -----------
张三 10 0 10
张三 12 10 12
张三 13 -20 45
李四 14 30 -16
张三 15 40 20
李四 16 -90 90
张三 70 70 20
李四 80 8 162
李四 90 3 249
李四 10 -10 269(所影响的行数为 10 行)
--*/
//-- os: win 2000 server
//-- 调试器: access 2000
//-- 注释格式符号: //---
//-- 模拟表的创建
create table T1
(
name char(10) ,
series int ,
increase int ,
decrease int
)//--在 access 下 偶不知道怎么执行批量sql 只好一行一行的粘贴执行,如果那位大虾知道清告知一下
insert into T1 values("张三" , 1 , 20 ,10 );
insert into T1 values("张三" , 2 , 20 ,10 );
insert into T1 values("张三" , 3 , 20 ,10 );
insert into T1 values("张三" , 4 , 20 ,10 );
insert into T1 values("张三" , 5 , 20 ,10 );
insert into T1 values("李四" , 1 , 20 ,10 );
insert into T1 values("李四" , 2 , 30 ,20 );
insert into T1 values("李四" , 3 , 40 ,30 );
insert into T1 values("李四" , 4 , 50 ,40 );
insert into T1 values("王老五" , 1 , 10 ,10 );//-- sql 的调试, 原来写的有bug, 应该是 B.series <= A.series 而不是 >= 下面是修正后的结果。
//-- 同时减少的字段名字 改成 decrease
select A.name ,A.series, A.increase , A.decrease , sum(B.increase - B.decrease) As balance
from T1 A , T1 B where A.name = B.name and B.series <= A.series
group by A.name ,A.series ,A.increase , A.decrease //-- sql 的调试, 原来写的有bug, 应该是 B.series <= A.series 而不是 >= 下面是修正后的结果。
//-- 同时减少的字段名字 改成 decrease select A.name ,A.series, A.increase , A.decrease ,
(select sum(B.increase - B.decrease) from T1 B where B.name = A.name and B.series <= A.series ) As balance
from T1 A
//-- 两条sql都能够执行。j结果如下name series increase decrease balance
张三 1 20 10 10
张三 2 20 10 20
张三 3 20 10 30
张三 4 20 10 40
张三 5 20 10 50
李四 1 20 10 10
李四 2 30 20 20
李四 3 40 30 30
李四 4 50 40 40
王老五 1 10 10 0
也就是说
insert into T1 values("张三" , 1 , 20 ,10 );
insert into T1 values("张三" , 2 , 20 ,10 );
insert into T1 values("张三" , 3 , 20 ,10 );
insert into T1 values("张三" , 4 , 20 ,10 );
insert into T1 values("张三" , 5 , 20 ,10 );
insert into T1 values("李四" , 6 , 20 ,10 );
insert into T1 values("李四" , 7 , 30 ,20 );
insert into T1 values("李四" , 8 , 40 ,30 );
insert into T1 values("李四" , 9 , 50 ,40 );
insert into T1 values("王老五" , 10 , 10 ,10 );这样的数据也可以 符合要求,不过感觉比较乱 ,不好看而已。
select identity(int,1,1) as id ,* into #t from 表select #t. 姓名,#t.增加金额,#t.增加金额余额
=(select sum(增加金额-增加金额) from #t where id<=a.id) from #t adrop table #t
試下上面的
沒留意出了點問題
select a. 姓名,a.增加金额,a.增加金额余额
=(select sum(增加金额-增加金额) from #t where id<=a.id) from #t a---這樣
create table test1
(
iid int identity(1,1),
ss varchar(10),
sname int,
sname1 int,
)
insert into test1
select '张三','23123','212'
union all
select '张三','112','123'
union all
select '张三','2323','212'
union all
select '张三','2313','212'
union all
select '张三','2313','212'
union all
select '张三','2123','212'
union all
select '张三','23123','212'
*/
--select * from test1
select a.* ,余额=(select sum(sname)-sum(sname1) from test1 b where b.iid<=a.iid) from test1 a
if not exists(select * from sysobjects where name='tb')
begin
create table tb
(
[ID] int identity(1,1),
[Name] varchar(50) default '',
addmoney money default 0.00,
submoney money default 0.00
)
end
else
begin
truncate table tb
endinsert tb([Name],addmoney,submoney)
select '张三',100,86
union
select '张三',200,34
union
select '张三',400,356
union
select '张三',67,24select A.*,(select sum(addmoney)-sum(submoney) from tb as B where B.ID<=A.ID) as [left] from tb as A
不一定是先录入的纪录就是会先出来或者后出来.