这种表怎么更新呢
这种表怎么更新呢
TheCode ItemCode TheValue
001 10 12
001 11 12
001 12 2
001 13 4
002 11 1
002 12 4
004 11 5
005 11 1
005 12 6
005 13 6
其它ItemCode 的不管比如itemCode <=10 itemCode> 13
按TheCode 把 itemCode =11 or13 or 12
的TheValue加起来 更新 itemCode = 11 的TheValue
并把itemCode=12 和13 的 theValue 清为0
上述结果为:
TheCode ItemCode TheValue
001 10 12
001 11 18
001 12 0
001 13 0
002 11 5
002 12 0
004 11 5
005 11 13
005 12 0
005 13 0
[解决办法]
create table tree(TheCode varchar(10),ItemCode varchar(10),TheValue int)
insert tree
select '001 ', '11 ',12 union all
select '001 ', '12 ',2 union all
select '001 ', '13 ',4 union all
select '002 ', '11 ',1 union all
select '002 ', '12 ',4 union all
select '004 ', '11 ',5 union all
select '005 ', '11 ',1 union all
select '005 ', '12 ',6 union all
select '005 ', '13 ',6
go
select * from tree
update tree set TheValue=sum from tree,(select TheCode as TheCode,sum(TheValue) as sum from tree where ItemCode in ( '11 ', '12 ', '13 ') Group by TheCode) t
where tree.TheCode=t.TheCode and tree.ItemCode= '11 '
update tree set TheValue=0 where ItemCode <> 11
select * from tree
go
drop table tree
