create table test1 
( 
id1  varchar(10), 
name1 varchar(20) 
) 
insert into test1 select '01','a1' 
insert into test1 select '02','a2' 

create table test2 
( 
id2  varchar(10), 
name2 varchar(20) 
) 
insert into test2 select '03','b1' 
insert into test2 select '04','b2' 

select *
from test1 a left join test2 b on a.id1+2 = b.id2


drop table test1 
drop table test2 
/*
id1        name1                id2        name2                
---------- -------------------- ---------- -------------------- 
01         a1                   03         b1
02         a2                   04         b2

（所影响的行数为 2 行）

------解决方案--------------------
SQL code
create table test1 
( 
id1  varchar(10), 
name1 varchar(20) 
) 
insert into test1 select '01','a1' 
insert into test1 select '02','a2' 

create table test2 
( 
id2  varchar(10), 
name2 varchar(20) 
) 
insert into test2 select '03','b1' 
insert into test2 select '04','b2' 

select m.id1 , m.name1,n.id2 , n.name2 from
(select * , px =  (select count(1) from test1 where id1 < t.id1) + 1 from test1 t) m
full join
(select * , px =  (select count(1) from test2 where id2 < t.id2) + 1 from test2 t) n
on m.px = n.px

/* 

id1        name1                id2        name2                
---------- -------------------- ---------- -------------------- 
01         a1                   03         b1
02         a2                   04         b2

（所影响的行数为 2 行）

*/ 


drop table test1 
drop table test2

------解决方案--------------------
SQL code
create table test1 
( 
id1  varchar(10), 
name1 varchar(20) 
) 
insert into test1 select '01','a1' 
insert into test1 select '02','a2' 

create table test2 
( 
id2  varchar(10), 
name2 varchar(20) 
) 
insert into test2 select '03','b1' 
insert into test2 select '04','b2' 
--2000
select identity(int,1,1) as id,* into #1  from test1
select identity(int,1,1) as id,* into #2  from test2
select id1,name1,id2,name2 
from #1 a left join #2 b on a.id=B.id
--2005 
select id1,name1,id2,name2
from (select ROW_NUMBER()over(order by getdate()) as rn ,* from test1) a
left join (select ROW_NUMBER()over(order by getdate()) as rn ,* from test2) b
on a.rn=b.rn

------解决方案--------------------
生成序列pid

然后(pid-1)/2分组

------解决方案--------------------
如果是2005,改用row_number()
SQL code
select m.id1 , m.name1,n.id2 , n.name2 from
(select * , px =  row_number() over(order by id1) from test1 t) m
full join
(select * , px =  row_number() over(order by id2) from test2 t) n
on m.px = n.px

------解决方案--------------------
探讨

生成序列pid

然后(pid-1)/2分组


------解决方案--------------------
SQL code
create table test1 
( 
id1  varchar(10), 
name1 varchar(20) 
) 
insert into test1 select '01','a1' 
insert into test1 select '02','a2' 

create table test2 
( 
id2  varchar(10), 
name2 varchar(20) 
) 
insert into test2 select '03','b1' 
insert into test2 select '04','b2' 

select
 id1,name1,id2,name2
from
 (select row_number()over(order by getdate()) as id ,* from test1) a
left