declare @t table(a int,b int,c int,d int)insert @t select 1,1,1,0 union all select 2,3,0,1select sum(c) as sum_c,a,b into #t from @t group by a,bupdate @t set d=0 from @t as a join #t as b on a.a=b.a and a.b=b.b where sum_c=0 update @t set d=1 from @t as a join #t as b on a.a=b.a and a.b=b.b where sum_c<>0select * from @t
--测试表 DECLARE @T TABLE(A INT,B INT,C INT,D INT) INSERT @T SELECT 1,1,0,NULL UNION ALL SELECT 1,2,4,NULL UNION ALL SELECT 1,2,4,NULL --更新语句 UPDATE T1 SET D=CASE WHEN (SELECT SUM(C) FROM @T WHERE T1.A=A AND T1.B=B)=0 THEN 0 ELSE 1 END FROM @T T1 --结果 SELECT * FROM @T
declare @t table(a int,b int,c int,d int)insert @t select 1,1,1,0
union all select 2,3,0,1select sum(c) as sum_c,a,b
into #t
from @t
group by a,bupdate @t set d=0 from @t as a join #t as b on a.a=b.a and a.b=b.b where sum_c=0
update @t set d=1 from @t as a join #t as b on a.a=b.a and a.b=b.b where sum_c<>0select * from @t
DECLARE @T TABLE(A INT,B INT,C INT,D INT)
INSERT @T SELECT 1,1,0,NULL
UNION ALL SELECT 1,2,4,NULL
UNION ALL SELECT 1,2,4,NULL
--更新语句
UPDATE T1
SET D=CASE WHEN (SELECT SUM(C) FROM @T WHERE T1.A=A AND T1.B=B)=0 THEN 0 ELSE 1 END
FROM @T T1
--结果
SELECT * FROM @T
答案比我的好