高分求教：紧急求助一个update语句【不要用游标】

Question

drop table tabA
go
create table tabA(c varchar(20),nQty numeric(12,4))
insert into tabA
select 'a',13 union all
select 'a',17 union all
select 'b',20 union all
select 'b',20drop table tabB
go
create table tabB(c varchar(20) primary key,nQty numeric(12,4))
insert into tabB
select 'a',100 union all
select 'b',30
如何根据tabB表内容更新tabA表内容为：
'a',0
'a',0
'b',0
'b',10
即A表中根据c字段按顺序减掉nQty的总和是B表中nQty的值，谢谢。

Answer 1

declare  @tabA table(c varchar(20),nQty numeric(12,4))
insert into @tabA
select 'a',13 union all
select 'a',17 union all
select 'b',20 union all
select 'b',20declare @tabB table(c varchar(20) primary key,nQty numeric(12,4))
insert into @tabB
select 'a',100 union all
select 'b',30
select a.c , case when   a.n -  b.nQty >= 0 then a.n -  b.nQty else 0 end  as n
from (
select c,n=(select SUM(n) from (
select ROW_NUMBER() over (order by nQty) r ,c,nQty as n from @tabA) b where b.r<= a.r and b.c = a.c)
from (
select ROW_NUMBER() over (order by nQty) r ,c,nQty as n from @tabA
) a 
) a inner join @tabB b on a.c = b.c-----------------------
a 0.0000
a 0.0000
b 0.0000
b 10.0000

Answer 2

最后一条怎么会是-10呢？
A表中第一个b为20，小于B表30，所以nQty为减20，结果为0
第二个b为20，减去B表剩下的30-20=10，结果为10啊。

Answer 3

--楼主可以看看是否为这样 use tempdb
go
if OBJECT_ID('tabA','U')is not null
drop table tabA
go
create table tabA(ID int identity,c varchar(20),nQty numeric(12,4))--唯一列判断先后顺序（时间或标识列）
insert into tabA
select 'a',13 union all
select 'a',17 union all
select 'b',20 union all
select 'b',20
go
if OBJECT_ID('tabB','U')is not null
drop table tabB
go
create table tabB(c varchar(20) primary key,nQty numeric(12,4))
insert into tabB
select 'a',100 union all
select 'b',30
go
--查询
select 
a.*,case when b.nQty>a.sumQty then 0 else a.sumQty-b.nQty end as upCol
from (select *,isnull((select SUM(nQty) from tabA where c=c.c and ID<=c.ID),0) as sumQty from tabA as c) as a,tabB as b
where a.c=b.c and a.sumQty<b.nQty+a.nQty
/*
ID c nQty sumQty upCol
1 a 13.0000 13.0000 0.0000
2 a 17.0000 30.0000 0.0000
3 b 20.0000 20.0000 0.0000
4 b 20.0000 40.0000 10.0000
*/
--更新update tb
set nQty=ta.upCol
from 
(select 
a.*,case when b.nQty>a.sumQty then 0 else a.sumQty-b.nQty end as upCol
from (select *,isnull((select SUM(nQty) from tabA where c=c.c and ID<=c.ID),0) as sumQty from tabA as c) as a,tabB as b
where a.c=b.c and a.sumQty<b.nQty+a.nQty)ta
inner join tabA as tb on ta.ID=tb.ID

Answer 4

还是老兄读懂了我的意思。谢谢，我用的是sql2000，没有ROW_NUMBER，我加了个iID字段试了一下，可以:)
declare  @tabA table(iID int identity(1,1),c varchar(20),nQty numeric(12,4))
insert into @tabA
select 'a',13 union all
select 'a',17 union all
select 'b',17 union all
select 'b',20 union all
select 'a',71declare @tabB table(c varchar(20) primary key,nQty numeric(12,4))
insert into @tabB
select 'a',100 union all
select 'b',30
select a.c , case when   a.n -  b.nQty >= 0 then a.n -  b.nQty else 0 end  as n
from (
    select c,n=(select SUM(n) from (
        select iID r ,c,nQty as n from @tabA) b where b.r<= a.r and b.c = a.c)
    from (
        select iID r ,c,nQty as n from @tabA
    ) a 
) a inner join @tabB b on a.c = b.c

Answer 5

create table tabA(c varchar(20),nQty numeric(12,4))
insert into tabA
select 'a',13 union all
select 'a',17 union all
select 'b',20 union all
select 'b',20create table tabB(c varchar(20) primary key,nQty numeric(12,4))
insert into tabB
select 'a',100 union all
select 'b',30
gowith cte as
(
select *,rn = row_number() over (partition by c order by getdate())
from tabA
),cta as
(
select t.c,t.rn,nQty = e.nQty - (select sum(nQty) from cte where c = t.c and rn <= t.rn)
from cte t left join tabB e on t.c = e.c
)
select c,(case when nQty >= 0 then 0 else abs(nQty) end)nQty
from ctadrop table tabA,tabB
/*c                    nQty
-------------------- ---------------------------------------
a                    0.0000
a                    0.0000
b                    0.0000
b                    10.0000(4 行受影响)

Answer 6

create table tabA(c varchar(20),nQty numeric(12,4) , id int)
insert into tabA
select 'a',13,1 union all
select 'a',17,2 union all
select 'b',20,1 union all
select 'b',20,2create table tabB(c varchar(20) primary key,nQty numeric(12,4))
insert into tabB
select 'a',100 union all
select 'b',30 update tabA set nQty =
       (case when (select sum(nQty) from tabA where c = t.c and id <= t.id) <= p.nQty then 0 
            when (select sum(nQty) from tabA where c = t.c and id <= t.id) - p.nQty <= t.nqty then p.nQty - (select sum(nQty) from tabA where c = t.c and id <= t.id)
            else t.nqty
       end)
from tabA t , tabB p
where t.c = p.cselect * from tabAdrop table tabA , tabb/*
c                    nQty           id          
-------------------- -------------- ----------- 
a                    .0000          1
a                    .0000          2
b                    .0000          1
b                    -10.0000       2（所影响的行数为 4 行）
*/create table tabA(c varchar(20),nQty numeric(12,4) , id int)
insert into tabA
select 'a',13,1 union all
select 'a',17,2 union all
select 'b',20,1 union all
select 'b',20,2 union all
select 'b',20,3create table tabB(c varchar(20) primary key,nQty numeric(12,4))
insert into tabB
select 'a',100 union all
select 'b',30 update tabA set nQty =
       (case when (select sum(nQty) from tabA where c = t.c and id <= t.id) <= p.nQty then 0 
            when (select sum(nQty) from tabA where c = t.c and id <= t.id) - p.nQty <= t.nqty then p.nQty - (select sum(nQty) from tabA where c = t.c and id <= t.id)
            else t.nqty
       end)
from tabA t , tabB p
where t.c = p.cselect * from tabAdrop table tabA , tabb/*
c                    nQty           id          
-------------------- -------------- ----------- 
a                    .0000          1
a                    .0000          2
b                    .0000          1
b                    -10.0000       2
b                    20.0000        3（所影响的行数为 5 行）*/

Answer 7

如果你要的结果是10,把when (select sum(nQty) from tabA where c = t.c and id <= t.id) - p.nQty <= t.nqty then p.nQty - (select sum(nQty) from tabA where c = t.c and id <= t.id)-->when (select sum(nQty) from tabA where c = t.c and id <= t.id) - p.nQty <= t.nqty then (select sum(nQty) from tabA where c = t.c and id <= t.id) - p.nQty