更新同一张表
这种表怎么更新呢
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 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
（所影响的行数为 9 行）
*/
[解决办法]
----创建测试数据
declare @t table(TheCode varchar(10),ItemCode int, TheValue int)
insert @t
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
----更新
UPDATE a SET TheValue =
case
when not exists(select 1 from @t where TheCode = a.TheCode and ItemCode < a.ItemCode)
then (select sum(TheValue) from @t where TheCode = a.TheCode)
else 0
end
FROM @t as a
----查看
select * from @t
/*结果
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
*/
[解决办法]
select B.thecode,B.itemcode,A.su from
(select thecode,min(itemcode) mi,sum(thevalue) su from #wgz group by thecode)A
right join #wgz B on A.thecode=B.thecode and A.mi=B.itemcode
thecode itemcode su
---------- ----------- -----------
001 11 18
001 12 NULL
001 13 NULL
002 11 5
002 12 NULL
004 11 5
005 11 13
005 12 NULL
005 13 NULL
（所影响的行数为 9 行）
[解决办法]
.......后悔..后悔,慢了
