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

[mathsfan]

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',20

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
如何根据tabB表内容更新tabA表内容为：
'a',0
'a',0
'b',0
'b',10
即A表中根据c字段按顺序减掉nQty的总和是B表中nQty的值，谢谢。

[mathsfan]

分太少？？？？？？？？？

[中国风]

[Quote=引用楼主 mathsfan 的回复:]
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',20

drop table tabB
……
[/Quote]
最后一条是-10吧

[dawugui]

根据你的需求,你的A表需要加上个一可能区分先后顺序的字段才行.

[天-笑]

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',20

declare @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

[mathsfan]

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

[中国风]

--楼主可以看看是否为这样 

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

[mathsfan]

[Quote=引用 4 楼 shmilywcd 的回复:]
SQL code


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',20

declare @tabB table……
[/Quote]
还是老兄读懂了我的意思。谢谢，我用的是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',71

declare @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

[AcHerat]

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',20

create table tabB(c varchar(20) primary key,nQty numeric(12,4))
insert into tabB
select 'a',100 union all
select 'b',30
go

with 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 cta

drop table tabA,tabB


/*

c                    nQty
-------------------- ---------------------------------------
a                    0.0000
a                    0.0000
b                    0.0000
b                    10.0000

(4 行受影响)

[mathsfan]

[Quote=引用 6 楼 roy_88 的回复:]
SQL code
--楼主可以看看是否为这样

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 int……
[/Quote]
是的，要的就是这种效果。

[dawugui]

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

create 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.c

select * from tabA

drop 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,3

create 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.c

select * from tabA

drop table tabA , tabb

/*
c                    nQty           id          
-------------------- -------------- ----------- 
a                    .0000          1
a                    .0000          2
b                    .0000          1
b                    -10.0000       2
b                    20.0000        3

（所影响的行数为 5 行）

*/

[dawugui]

如果你要的结果是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