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

mathsfan 2011-03-19 11:07:31
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的值,谢谢。
...全文
69 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dawugui 2011-03-19
如果你要的结果是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
回复
dawugui 2011-03-19
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 行)

*/
回复
mathsfan 2011-03-19
[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]
是的,要的就是这种效果。
回复

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 2011-03-19
[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
回复
中国风 2011-03-19
--楼主可以看看是否为这样 

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 2011-03-19
最后一条怎么会是-10呢?
A表中第一个b为20,小于B表30,所以nQty为减20,结果为0
第二个b为20,减去B表剩下的30-20=10,结果为10啊。
回复
天-笑 2011-03-19


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
回复
dawugui 2011-03-19
根据你的需求,你的A表需要加上个一可能区分先后顺序的字段才行.
回复
中国风 2011-03-19
[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吧
回复
mathsfan 2011-03-19
分太少?????????
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-03-19 11:07
社区公告
暂无公告