日期:2014-05-18  浏览次数:20679 次

这样的SQL该怎么写?


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'

/*
要得到这样的报表:
id1 name1 id2 name2
01 a1 03 b1
02 a2 04 b2

*/


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' 

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