create table T(a int, b int) insert T select 1, 2 union all select 1, null union all select 2, 3 union all select 1, null union all select 2, null union all select 3, 4 union all select 3, nullselect T.* from T inner join T as tmp on T.a=tmp.a where T.b is not null--result a b ----------- ----------- 1 2 1 2 1 2 2 3 2 3 3 4 3 4(7 row(s) affected)
create table T(a int, b int) insert T select 1, 2 union all select 1, null union all select 2, 3 union all select 1, null union all select 2, null union all select 3, 4 union all select 3, nullselect a, b=case when b is null then (select b from T where a=tmp.a and b is not null)else b end from T tmp--result a b ----------- ----------- 1 2 1 2 2 3 1 2 2 3 3 4 3 4(7 row(s) affected)
--更新create table T(a int, b int) insert T select 1, 2 union all select 1, null union all select 2, 3 union all select 1, null union all select 2, null union all select 3, 4 union all select 3, nullupdate T set b=tmp.b from T, T as tmp where T.b is null and T.a=tmp.a and tmp.b is not nullselect * from T--result a b ----------- ----------- 1 2 1 2 2 3 1 2 2 3 3 4 3 4(7 row(s) affected)
select a,b=(case when b is null then a+1 else b end) from test--测试结果 a b ------------ 1 2 1 2 2 3 1 2 2 3 3 4 3 4
insert T select 1, 2
union all select 1, null
union all select 2, 3
union all select 1, null
union all select 2, null
union all select 3, 4
union all select 3, nullselect T.* from T
inner join T as tmp on T.a=tmp.a
where T.b is not null--result
a b
----------- -----------
1 2
1 2
1 2
2 3
2 3
3 4
3 4(7 row(s) affected)
insert T select 1, 2
union all select 1, null
union all select 2, 3
union all select 1, null
union all select 2, null
union all select 3, 4
union all select 3, nullselect a,
b=case when b is null then (select b from T where a=tmp.a and b is not null)else b end
from T tmp--result
a b
----------- -----------
1 2
1 2
2 3
1 2
2 3
3 4
3 4(7 row(s) affected)
insert T select 1, 2
union all select 1, null
union all select 2, 3
union all select 1, null
union all select 2, null
union all select 3, 4
union all select 3, nullupdate T set b=tmp.b
from T, T as tmp
where T.b is null and T.a=tmp.a and tmp.b is not nullselect * from T--result
a b
----------- -----------
1 2
1 2
2 3
1 2
2 3
3 4
3 4(7 row(s) affected)
from test--测试结果
a b
------------
1 2
1 2
2 3
1 2
2 3
3 4
3 4