求一条一句，先谢谢了

表1
bano,eqno,qty
ba,ea,100
ba,ed,100
bc,ea,50
be,ef,50
----------------------------

表2
bano,eqno,jhs,wcs
ba,ea,24,0
ba,ea,24,0
ba,ea,24,0
ba,ea,24,0
ba,ea,24,0
ba,ea,14,0
ba,ed,30,0
ba,ed,20,0
bc,ea,12,0
bc,ea,12,0
bc,ea,12,0
bc,ea,12,0
bc,ea,2,0
be,ef,24,0
be,ef,24,0
be,ef,24,0
be,ef,24,0
----------------------------
bano,eqno为varchar类型，jhs,wcs,qty为int类型


======================
需要下面结果
表2
bano,eqno,jhs,wcs
ba,ea,24,24
ba,ea,24,24
ba,ea,24,24
ba,ea,24,24
ba,ea,24,4
ba,ea,14,0
ba,ed,30,30
ba,ed,20,20
bc,ea,12,12
bc,ea,12,12
bc,ea,12,12
bc,ea,12,12
bc,ea,2,2
be,ef,24,24
be,ef,24,24
be,ef,24,2
be,ef,24,0



意思是说
当表2里的bano,eqno字段和表1相同 and 表1的qty > 0 时候修改表2中wcs字段(wcs字段数值不能大于表1的qty字段，同时也不能大于表2的jhs),修改表2wcs字段后，要将表1的qty-wcs

表2没有一个大小列或主健？

有一个varchar类型主键reid

SQL code

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T1') is null
    drop table #T1
Go
Create table #T1([bano] nvarchar(2),[eqno] nvarchar(2),[qty] int)
Insert #T1
select N'ba',N'ea',100 union all
select N'ba',N'ed',100 union all
select N'bc',N'ea',50 union all
select N'be',N'ef',50
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T2') is null
    drop table #T2
Go
Create table #T2([bano] nvarchar(2),[eqno] nvarchar(2),[jhs] int,[wcs] int)
Insert #T2
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',14,0 union all
select N'ba',N'ed',30,0 union all
select N'ba',N'ed',20,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',2,0 union all
select N'be',N'ef',24,0 union all
select N'be',N'ef',24,0 union all
select N'be',N'ef',24,0 union all
select N'be',N'ef',24,0
Go
;with a
as
(Select *,row=ROW_NUMBER()over(partition by [bano],[eqno] order by getdate()) from #T2)
,a2
as
(select *,sum2=(select SUM([jhs]) from a where [bano]=b.[bano] and [eqno]=b.[eqno] and row<=b.row) from a as b)
update a2
set wcs=case when b.qty>a2.sum2 then a2.jhs else b.qty-(a2.sum2-a2.jhs) end
from a2 
inner join #T1 as b on a2.bano=b.bano and a2.eqno=b.eqno
where b.qty>a2.sum2-a2.jhs

go
update a
set qty=a.qty-b.qty
from #t1  as a ,(select[bano],[eqno],SUM(wcs) as qty from #T2 group by [bano],[eqno]) as b 
where a.bano=b.bano and a.eqno=b.eqno

go
select * from #T1
/*
bano    eqno    qty
ba    ea    0
ba    ed    50
bc    ea    0
be    ef    0
*/
select * from #T2
/*
bano    eqno    jhs    wcs
ba    ea    24    24
ba    ea    24    24
ba    ea    24    24
ba    ea    24    24
ba    ea    24    4
ba    ea    14    0
ba    ed    30    30
ba    ed    20    20
bc    ea    12    12
bc    ea    12    12
bc    ea    12    12
bc    ea    12    12
bc    ea    2    2
be    ef    24    24
be    ef    24    24
be    ef    24    2
be    ef    24    0
*/

SQL2005以上版本用，以上方法

SQL2000用游标

我使用的是SQL2000，游标怎么用呀

SQL code

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T1') is null
    drop table #T1
Go
Create table #T1([bano] nvarchar(10),[eqno] nvarchar(10),[qty] int)
Insert #T1
select N'ba',N'ea',100 union all
select N'ba',N'ed',100 union all
select N'bc',N'ea',50 union all
select N'be',N'ef',50
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T2') is null
    drop table #T2
Go
Create table #T2([bano] nvarchar(10),[eqno] nvarchar(10),[jhs] int,[wcs] int)
Insert #T2
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',24,0 union all
select N'ba',N'ea',14,0 union all
select N'ba',N'ed',30,0 union all
select N'ba',N'ed',20,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',12,0 union all
select N'bc',N'ea',2,0 union all
select N'be',N'ef',24,0 union all
select N'be',N'ef',24,0 union all
select N'be',N'ef',24,0 union all
select N'be',N'ef',24,0
go
declare cur cursor for 
select * from #T1
declare @bano nvarchar(50),@eqno nvarchar(50),@qty int,@qty2 int,@wcs int
open cur
fetch next from cur into @bano,@eqno,@qty
while @@FETCH_STATUS=0
begin
update #T2 
set @wcs=[wcs]=case when @qty>=[jhs] and  @qty>0 then [jhs] when @qty>0 then @qty else 0 end,
@qty=@qty-@wcs
where [bano]=@bano and [eqno]=@eqno
update #T1 set[qty] =@qty where [bano]=@bano and [eqno]=@eqno
fetch next from cur into @bano,@eqno,@qty
end
close cur
deallocate cur

select * from #T2
select * from #T1