select T.id
,A.name
,A.[money] as 'oldmoney'
,A.[money]-(select sum(T.pay)
from B
where name=T.name
and id<=T.id) as 'newmoney'
from A
join B T on A.name=T.name
order by T.id
,A.name
,A.[money] as 'oldmoney'
,A.[money]-(select sum(T.pay)
from B
where name=T.name
and id<=T.id) as 'newmoney'
from A
join B T on A.name=T.name
order by T.id
oldmoney=b.name-isnull((select sum(pay) from 表A where name=a.name and id<a.id),0),
newmoney=b.name-isnull((select sum(pay) from 表A where name=a.name and id<=a.id),0)
into 表C
from 表B a left join 表A b on a.name=b.name
--test
select * from 表C
b.id,
b.name,
oldmoney = a.money-isnull(b.pay1,0),
newmoney = a.money-isnull(b.pay2,0)
from
表A a
(select
id,
name,
pay1=(select sum(pay) from 表B where id< c.id and name=c.name),
pay2=(select sum(pay) from 表B where id<=c.id and name=c.name)
from
表B c) b
where
a.name=b.name
order by
b.id
,A.name
,A.[money] as 'oldmoney'
,A.[money]-(select sum(pay)
from B
where name=T.name
and id<=T.id) as 'newmoney'
from A
join B T on A.name=T.name
order by T.id
(
id int,
name varchar(10),
[money] int
)
create table B
(
id int,
name varchar(10),
pay int
)
insert A
select 1 ,'man1', 1000 union
select 2 ,'man2', 2000
insert B
select 1 ,'man1', 100 union
select 2 ,'man1', 200 union
select 3 ,'man1', 300 union
select 4 ,'man2', 300 union
select 5 ,'man2', 400 --查询
select T.id
,A.name
,A.[money] as 'oldmoney'
,A.[money]-(select sum(pay)
from B
where name=T.name
and id<=T.id) as 'newmoney'
from A
join B T on A.name=T.name
order by T.id--删除测试环境
drop table A,B--结果
/*
id name oldmoney newmoney
----------- ---------- ----------- -----------
1 man1 1000 900
2 man1 1000 700
3 man1 1000 400
4 man2 2000 1700
5 man2 2000 1300(5 row(s) affected)
*/
insert into 表A select 1,'man1',1000
insert into 表A select 2,'man2',2000create table 表B(id int,name varchar(10),pay int)
insert into 表B select 1,'man1',100
insert into 表B select 2,'man1',200
insert into 表B select 3,'man1',300
insert into 表B select 4,'man2',300
insert into 表B select 5,'man2',400select
b.id,
b.name,
oldmoney = a.money-isnull(b.pay1,0),
newmoney = a.money-isnull(b.pay2,0)
into
表C
from
表A a,
(select
id,
name,
pay1=(select sum(pay) from 表B where id< c.id and name=c.name),
pay2=(select sum(pay) from 表B where id<=c.id and name=c.name)
from
表B c) b
where
a.name=b.name
order by
b.id
select * from 表C
/*
id name oldmoney newmoney
------ ------- ----------- -----------
1 man1 1000 900
2 man1 900 700
3 man1 700 400
4 man2 2000 1700
5 man2 1700 1300
*/drop table 表A,表B,表C
(
id int,
name varchar(10),
[money] int
)
create table 表B
(
id int,
name varchar(10),
pay int
)
insert 表A
select 1 ,'man1', 1000 union
select 2 ,'man2', 2000
insert 表B
select 1 ,'man1', 100 union
select 2 ,'man1', 200 union
select 3 ,'man1', 300 union
select 4 ,'man2', 300 union
select 5 ,'man2', 400
go
select a.id,a.name,
oldmoney=b.money-isnull((select sum(pay) from 表B where name=a.name and id<a.id),0),
newmoney=b.money-isnull((select sum(pay) from 表B where name=a.name and id<=a.id),0)
into 表C
from 表B a left join 表A b on a.name=b.name
--group by a.id,a.name,b.money
--test
select * from 表C
/*
id name oldmoney newmoney
----------- ---------- ----------- -----------
1 man1 1000 900
2 man1 900 700
3 man1 700 400
4 man2 2000 1700
5 man2 1700 1300(所影响的行数为 5 行)
*/
--drop table
drop table 表A,表B,表C
insert into a select 1,'man1',1000
insert into a select 2,'man2',2000create table b (id int ,name varchar(10),pay int)
insert into b select 1,'man1',100
insert into b select 2,'man1',200
insert into b select 3,'man1',300
insert into b select 4,'man2',300
insert into b select 5,'man2',400select c.id,c.name,oldmoney=money
,newmoney=money -(select sum(pay) from b where id<=c.id and name=c.name)
from a,b c
where a.name=c.name
oldmoney=b.money-isnull((select sum(pay) from 表A where name=a.name and id<a.id),0)
=========>只要把上面表A改为表B
还有下面一个地方也是同样。