不用循环，游标。这个sql 怎么写？

[tmxkdldw]

如a表（贷款表）
合同号hth 总额ze
001 456
002 387

b表(还款表)
合同号hth 顺序号xh 金额je
001 1 34
001 2 66
001 3 50
002 1 20
002 2 10
现在想求出如下结果(余额 ye 又还款顺序的变化而边)

合同号hth 顺序号xh 金额je 余额 ye
001 1 34 422
001 2 66 356
001 3 50 306
002 1 20 367
002 2 10 357

[chenjunjarysky]

学习

[Zack999]

create table #a(
hth int,
ze int
)
create table #b(
hth int,
xh int,
je int
)
insert into #a
select 1,456 union all
select 2,387
insert into #b
select 1,1,34 union all
select 1,2,66 union all
select 1,3,50 union all
select 2,1,20 union all
select 2,2,10

select #a.hth,b.xh,b.je,(select #a.ze-(select sum(je) from #b where b.hth=#b.hth and b.xh>=#b.xh)) as ye
from #a,#b as b
where #a.hth=b.hth

drop table #a
drop table #b

[拓狼]

--生成测试数据
declare @a table(hth varchar(100),je int)
declare @b table(hth varchar(100),xh int,je int)

insert into @a select '001', 456
insert into @a select '002', 387

insert into @b select '001', 1, 34
insert into @b select '001', 2, 66
insert into @b select '001', 3, 50
insert into @b select '002', 1, 20
insert into @b select '002', 2, 10

--解决方法
select d.hth,xh,d.je,ye=a.je-d.num from @a a inner join (
select *,num=(select sum(c.je) from @b c where b.hth=c.hth and c.xh<=b.xh) from @b b)d
on a.hth=d.hth

[拓狼]

或者简单一点
select b.*,ye=a.je-(select sum(c.je) from @b c where b.hth=c.hth and c.xh<=b.xh) from @b b inner join @a a
on a.hth=b.hth

[hhhdyj]

declare @a table (hth varchar(3), ze int)
declare @b table (hth varchar(3), xh int, je int)

insert into @a
select '001', 456 union select '002', 378

insert into @b
select '001', 1, 34 union select '001', 2, 66 union select '001', 3, 50
union select '002', 1, 20 union select '002', 2, 10

select * from @a
select * from @b

select a.hth, b.xh, b.je,
a.ze - (select sum(je) from @b where hth = a.hth and xh <= b.xh) as ye
from @a a inner join @b b on a.hth = b.hth
order by a.hth, b.xh

[stou]

create table aa
(
hth int,
ze int
)
go
insert aa(hth,ze) values(1,456)
insert aa(hth,ze) values(2,387)
go
create table bb
(
hth int,
xh int,
je int
)
go
insert bb select 001 , 1 , 34
union all select 001 , 2 , 66
union all select 001 , 3 , 50
union all select 002 , 1 , 20
union all select 002 , 2 , 10

select b.*,aa.ze-(select sum(je) from bb where hth=b.hth and xh<=b.xh ) from bb b,aa where b.hth=aa.hth

[StarRains]

select b.*,a.ze-(select sum(bb.je) from b bb where b.hth=bb.hth and bb.xh<=b.xh) ye from b inner join a on a.hth=b.hth