22,300
社区成员




DECLARE @t TABLE(id int identity,a nvarchar(4),b nvarchar(4))
INSERT INTO @t
SELECT N'小红',N'100' UNION ALL
SELECT N'小红',NULL UNION ALL
SELECT N'小红',NULL UNION ALL
SELECT N'小红',NULL UNION ALL
SELECT N'小红',N'100' UNION ALL
SELECT N'小红',NULL UNION ALL
SELECT N'小红',NULL UNION ALL
SELECT N'小明',NULL UNION ALL
SELECT N'小明',NULL UNION ALL
SELECT N'小兰',N'300' UNION ALL
SELECT N'小兰',NULL
;WITH s AS ( -- 假定每个a的非空b值都相等
SELECT DISTINCT a,b
FROM @t
WHERE b IS NOT NULL
)
UPDATE @t
SET t.b = s.b
FROM @t t, s
WHERE t.a = s.a
SELECT * FROM @t
id a b
----------- ---- ----
1 小红 100
2 小红 100
3 小红 100
4 小红 100
5 小红 100
6 小红 100
7 小红 100
8 小明 NULL
9 小明 NULL
10 小兰 300
11 小兰 300
update x set x.b= y.b
from table1 x left join
(
Select a,MAX(b) b from table1 group by a
)
y on x.a=y.a
update table1 set b=
case when a='小红' then 100
when a='小兰' then 300 else b end
from table1
update table1 set b=
case when a='小红' then 100
when a='小明' then 300 else b end
from table1