update问题
怎么利用这次的update结果应用到下一个update ????
Update #SSItem
set c = a - (select sum(b+ c) from #SSItem s1 where s1.name=s.name)
From #SSItem s
更新前
name a b c
a 5 2 0
a 5 2 0
b 6 3 0
更新后应该
a 5 2 1
a 5 2 0
b 6 3 3
我运行的结果却是
a 5 2 1
a 5 2 1
b 6 3 3
[解决办法]
--如果有唯一ID，可以:
--创建测试表
create table t(name varchar(100), a int, b int, c int,id int identity(1,1))
--追加测试数据
insert into T select 'a ', 5, 2, 0
insert into T select 'a ', 5, 2, 0
insert into T select 'b ', 6, 3, 0
--更新
Update T
set c = a - (select sum(b+ c) from T as s1 where s1.name=s.name)
From T as s
where id = (select min(id) from T where name=s.name)
select * from T
--删除测试表
drop table T
[解决办法]
create table t(name varchar(8),a int,b int,c int)
insert into t select 'a ',5,2,0
insert into t select 'a ',5,2,0
insert into t select 'b ',6,3,0
go
alter table t add id int identity(1,1)
go
update n
set
c=a-(select sum(b+c) from t where name=n.name)
from
t n
where
not exists(select 1 from t where name=n.name and ID <n.ID)
go
alter table t drop column ID
go
select * from t
go
/*
name a b c
-------- ----------- ----------- -----------
a 5 2 1
a 5 2 0
b 6 3 3
*/
drop table t
go
[解决办法]
这两天 子陌 大大 很活跃啊
支持下～
[解决办法]
create table tb (name varchar(10),a int,b int,c int)
insert into tb values( 'a ', 5, 2, 0)
insert into tb values( 'a ', 5, 2, 0)
insert into tb values( 'b ', 6, 3, 0)
go
select id=identity(int,1,1) , * into temp from tb
update temp
set c = t.c
from temp,
(
select a.id,a.name,a.a,b.b,a.a-b.b c from temp a,
(select name,min(id) id ,sum(b) b from temp group by name) b
where a.name = b.name and a.id = b.id
) t
where temp.id = t.id
delete from tb
insert into tb select name,a,b,c from temp
select * from tb
drop table tb,temp
/*
name a b c
---------- ----------- ----------- -----------
a 5 2 1
a 5 2 0
b 6 3 3
（所影响的行数为 3 行）
*/
[解决办法]
狂顶
·
[解决办法]
up
