更新同一张表
这种表怎么更新呢
TheCode ItemCode TheValue
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
按TheCode 把 itemCode =11 or13 or 12
的TheValue加起来 更新 itemCode = 11 的TheValue
并把itemCode=12 和13 的 theValue 清为0
上述结果为:
TheCode ItemCode TheValue
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 tb(TheCode varchar(10),ItemCode int,TheValue int)
insert tb 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
select * from tb
update tb set TheValue=b.TheValue from
(
select tb.TheCode,tb.ItemCode,TheValue=isnull(a.TheValue,0) from tb left outer join
(
select TheCode,TheValue=sum(TheValue) from tb
group by TheCode
)a on tb.TheCode=a.TheCode and ItemCode=11
)b where tb.TheCode=b.TheCode and tb.ItemCode=b.ItemCode
select * from tb
drop table tb
/*
TheCode ItemCode Th