一个超级难的行合并
有一个表T1，在T1中有两个列C1,C2，表中的数据如下
C1 C2
1 1
4 NULL
NULL 5
7 NULL
NULL 9
12 12
14 NULL
NULL 16
我想通过一个select语句来得到下面的结果：
C1 C2
1 1
4 5
7 9
12 12
14 16
请问这个select语句怎么写？
[解决办法]
declare @t table (
C1 int,
C2 int
)
insert @t select
1 , 1
union all select
4 , NULL
union all select
NULL , 5
union all select
7 , NULL
union all select
NULL , 9
union all select
12 , 12
union all select
14 , NULL
union all select
NULL , 16
select IDENTITY(int,1,1) as id,*
into #
from @t
select C1,C2 from # where C1 is not null and C2 is not null
union all
select T2.C1,T1.C2 from # t1 full join # t2
on (select count(*) from # where C1 is null and Id <=T1.Id)=(select count(*) from # where C2 is null and Id <=T2.Id)
where T1.C1 is null
and T2.C2 is null
drop table #
--结果
C1 C2
----------- -----------
1 1
12 12
4 5
7 9
14 16
（所影响的行数为 5 行）
[解决办法]
按照楼主的排序
declare @t table (
C1 int,
C2 int
)
insert @t select
1 , 1
union all select
4 , NULL
union all select
NULL , 5
union all select
7 , NULL
union all select
NULL , 9
union all select
12 , 12
union all select
14 , NULL
union all select
NULL , 16
union all select
17 , Null--加多一行
select IDENTITY(int,1,1) as id,*
into #
from @t
select C1,C2
from (
select C1,C2,Id from # where C1 is not null and C2 is not null
union all
select T2.C1,T1.C2,isnull(T1.id,T2.Id) as Id from # t1 full join # t2
on (select count(*) from # where C1 is null and Id <=T1.Id)=(select count(*) from # where C2 is null and Id <=T2.Id)
where T1.C1 is null
and T2.C2 is null
) as t
order by id
drop table #
--结果
C1 C2
----------- -----------
1 1
4 5
7 9
12 12
14 16
17 NULL
（所影响的行数为 6 行）
[解决办法]
select id = identity(int , 1,1) ,c1 into tb1 from t1 where c1 is not null
select id = identity(int , 1,1) ,c2 into tb2 from t1 where c2 is not null
--查询
select c1,c2 from tb1 , tb2 where t1.id = t2.id
--更改
drop table t1
select c1,c2 from tb1 , tb2 into t1 where t1.id = t2.id
[解决办法]
--测试数据：@T1
declare @T1 table(C1 int,C2 int)
insert @T1
select 1,1 union all
select 4,NULL union all
select NULL,5 union all
select 7,NULL union all
select NULL,9 union all
select 12,12 union all
select 14,NULL union all
select NULL,16
-- IF C1和C2是有序且唯一的 THEN
select a.C1,b.C2 from
(select CN=(select count(1) from @T1 where C1 <=a.C1), C1 from @T1 a where C1 is not null) a
full join
(select CN=(select count(1) from @T1 where C2 <=a.C2), C2 from @T1 a where C2 is not null) b
on a.CN=b.CN
/*
C1 C2
1 1
4 5
7 9
12 12
14 16
*/
-- ELSE
select id=identity(int,1,1),* into #temp from @T1
select a.C1,b.C2 from
(select CN=(select count(1) from #temp where C1 is not null and C1 <=a.C1), C1 from #temp a where C1 is not null) a
full join
(select CN=(select count(1) from #temp where C2 is not null and C2 <=a.C2), C2 from #temp a where C2 is not null) b
on a.CN=b.CN
/*
C1 C2
1 1
4 5
7 9
12 12
14 16
*/
drop table #temp
[解决办法]
create table tb(c1 int,c2 int)
insert into tb values(1 , 1)
insert into tb values(4 , NULL)
insert into tb values(NULL , 5)
insert into tb values(7 , NULL)
insert into tb values(NULL , 9)
insert into tb values(12 , 12)
insert into tb values(14 , NULL)
insert into tb values(NULL , 16)
go
select id = identity(int , 1,1) ,c1 into tb1 from tb where c1 is not null
select id = identity(int , 1,1) ,c2 into tb2 from tb where c2 is not null
--查询
select c1,c2 from tb1 , tb2 where tb1.id = tb2.id
/*
c1 c2
----------- -----------
1 1
4 5
7 9
12 12
14 16
（所影响的行数为 5 行）
*/
drop table tb,tb1,tb2
[解决办法]
--测试数据：@T1
declare @T1 table(C1 int,C2 int)
insert @T1
select 1,1 union all
select 4,NULL union all
select NULL,5 union all
select 7,NULL union all
select NULL,9 union all
select 12,12 union all
select 14,NULL union all
select NULL,16
-- IF C1和C2是有序且唯一的 THEN
select a.C1,b.C2 from
(select CN=(select count(1) from @T1 where C1 <=a.C1), C1 from @T1 a where C1 is not null) a
full join
(select CN=(select count(1) from @T1 where C2 <=a.C2), C2 from @T1 a where C2 is not null) b
on a.CN=b.CN
/*
C1 C2
1 1
4 5
7 9
12 12
14 16
*/
-- ELSE
select id=identity(int,1,1),* into #temp from @T1
--上面笔误，应该是id <=a.id：
select a.C1,b.C2 from
(select CN=(select count(1) from #temp where C1 is not null and id <=a.id), C1 from #temp a where C1 is not null) a
full join
(select CN=(select count(1) from #temp where C2 is not null and id <=a.id), C2 from #temp a where C2 is not null) b
on a.CN=b.CN
/*
C1 C2
1 1
4 5
7 9
12 12
14 16
*/
drop table #temp
[解决办法]
create table tb(c1 int,c2 int)
insert into tb values(1 , 1)
insert into tb values(4 , NULL)
insert into tb values(NULL , 5)
insert into tb values(7 , NULL)
insert into tb values(NULL , 9)
insert into tb values(12 , 12)
insert into tb values(14 , NULL)
insert into tb values(NULL , 16)
go
select id = identity(int , 1,1) ,c1 into tb1 from tb where c1 is not null
select id = identity(int , 1,1) ,c2 into tb2 from tb where c2 is not null
--更改
drop table tb
select c1,c2 into tb from tb1 , tb2 where tb1.id = tb2.id
select * from tb
/*
c1 c2
----------- -----------
1 1
4 5
7 9
12 12
14 16
（所影响的行数为 5 行）
*/
drop table tb,tb1,tb2
