tA:
COL1 col2 col3
A A A
B B B
C C Ctb:
colb1 colb2 colb3
D D D
E E E
得到
TC:
COL1 col2 col3 colb1 colb2 colb3
A A A D D D
B B B E E E
C C C表TA和表tb中的列没有直接的关系?
请问怎么来是实现??
COL1 col2 col3
A A A
B B B
C C Ctb:
colb1 colb2 colb3
D D D
E E E
得到
TC:
COL1 col2 col3 colb1 colb2 colb3
A A A D D D
B B B E E E
C C C表TA和表tb中的列没有直接的关系?
请问怎么来是实现??
if object_id('[tA]') is not null drop table [tA]
go
create table [tA]([COL1] varchar(1),[col2] varchar(1),[col3] varchar(1))
insert [tA]
select 'A','A','A' union all
select 'B','B','B' union all
select 'C','C','C'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([colb1] varchar(1),[colb2] varchar(1),[colb3] varchar(1))
insert [tb]
select 'D','D','D' union all
select 'E','E','E'
select
isnull(t1.col1,'') as col1,
isnull(t1.col2,'') as col2,
isnull(t1.col3,'') as col3,
isnull(t2.colb1,'') as colb1,
isnull(t2.colb2,'') as colb2,
isnull(t2.colb3,'') as colb3
from
(select *,px=(select count(1)+1 from ta where col1<a.col1) from ta a) t1
full join
(select *,px=(select count(1)+1 from tb where colb1<b.colb1) from tb b) t2
on t1.px=t2.px/**
col1 col2 col3 colb1 colb2 colb3
---- ---- ---- ----- ----- -----
A A A D D D
B B B E E E
C C C (所影响的行数为 3 行)
**/
--> 测试数据: @tA
declare @tA table (COL1 varchar(1),col2 varchar(1),col3 varchar(1))
insert into @tA
select 'A','A','A' union all
select 'B','B','B' union all
select 'C','C','C'
--> 测试数据: @tb
declare @tb table (colb1 varchar(1),colb2 varchar(1),colb3 varchar(1))
insert into @tb
select 'D','D','D' union all
select 'E','E','E'select COL1,col2,col3,colb1,colb2,colb3 from
(
select row_number() over(order by col1) as rn ,* from @tA
) a left join
(
select * from
(select row_number() over(order by colb1) as rn ,* from @tb)
b
)
c on a.rn=c.rn
/*
COL1 col2 col3 colb1 colb2 colb3
---- ---- ---- ----- ----- -----
A A A D D D
B B B E E E
C C C NULL NULL NULL
*/