字段是ip，ip1，iP2，ip1是null时ip2可能为null，也可能不是，如图T1

Question

上图T1

要有如下T2的效果

这是t2

select c.ip,ip1,ip2 from (
select a.ip as ip,a.ip1,b.ip2 from
(
select ip,ip1
from test4 where (ip2 is null and ip1 is not null) or(ip1 is not null and ip2 is not null)
) a
inner join
(select ip,ip2
from test4 where (ip1 is null and ip2 is not null) or(ip1 is not null and ip2 is not null)
) b on a.ip=b.ip) c

用这种方式可以达到效果，但是test4有几十万条数据会不会影响运行 效果，不知有没有更好的方法？？？？

Answer 1

select ip,max(ip1),max(ip2) from t1 group by ip