请问这个sql语句怎么写？新人请教了？
表的内容：test
a b
1 2
1
2 3
1
2
3 4
3
希望得到结果：
a b
1 2
1 2
2 3
1 2
2 3
3 4
3 4
在线等
[解决办法]
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, null
select 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, null
select 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)
[解决办法]
select a,b=(case when b is null then a+1 else b end)
from test
--测试结果
a b
------------
12
12
23
12
23
34
34
