关于两张表合并的问题SQL

Question

Create Table T1(
FName varchar(80)
)
Create Table T2(
FName varchar(80)
)Insert into T1
Select 'A'
Insert into T1
Select 'A'
Insert into T1
Select 'A'
Insert into T1
Select 'B'
Insert into T1
Select 'B'
Insert into T1
Select 'B'
Insert into T1
Select 'B'
Insert into T1
Select 'B'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'A'
Insert into T2
Select 'B'
Insert into T2
Select 'B'
Insert into T2
Select 'B'
Select * from T1
Select * from T2合并成这样结果
FName1  FName2
A A
A  A
A A
A
A
A
A
B B
B B
B B
B
B
B
C C
........

Answer 1

就是两张表, 要写个SQL语句, 并成一张表, 两个字段;表是按ABC排序的， 但是每张表， 数目不一样， 要保证B之前， 两列是一致的， 没有则为空；

Answer 2

Create Table T1(
    FName varchar(80)
)
Create Table T2(
    FName varchar(80)
)Insert into T1
    Select 'A'
Insert into T1
    Select 'A'
Insert into T1
    Select 'A'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'B'
Insert into T2
    Select 'B'
Insert into T2
    Select 'B'
select a.fname,b.fname from(
select fname,ROW_NUMBER ()over(partition by fname order by (select 1))rn from T1
)a full join(
select fname,ROW_NUMBER ()over(partition by fname order by (select 1))rn from T2
)b on a.FName =b.FName  and a.rn =b.rn 
/*
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A                                                                                A
A                                                                                A
A                                                                                A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
B                                                                                B
B                                                                                B
B                                                                                B
B                                                                                NULL
B                                                                                NULL(13 行受影响)*/

Answer 3

Create Table T1(
    FName varchar(80)
)
Create Table T2(
    FName varchar(80)
)Insert into T1
    Select 'A'
Insert into T1
    Select 'A'
Insert into T1
    Select 'A'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'B'
Insert into T2
    Select 'B'
Insert into T2
    Select 'B'
select a.fname as fname1,b.fname as fname2 from(
select fname,ROW_NUMBER ()over(partition by fname order by (select 1))rn from T1
)a full join(
select fname,ROW_NUMBER ()over(partition by fname order by (select 1))rn from T2
)b on a.FName =b.FName  and a.rn =b.rn 
/*
fname1                                                                           fname2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A                                                                                A
A                                                                                A
A                                                                                A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
B                                                                                B
B                                                                                B
B                                                                                B
B                                                                                NULL
B                                                                                NULL(13 行受影响)go
drop table t1,t2

Answer 4

select id=identity(int,1,1),* into #t1 from t1
select id=identity(int,1,1),* into #t2 from t2select 
  a.fname as fname1,b.fname as fname2
from
(
select fname,px=(select count(1) from #t1 where fname=t.fname and ID<T.ID) from #t1 t
)a
full join
(
select fname,px=(select count(1) from #t2 where fname=t.fname and ID<T.ID) from #t2 t
)b
on 
  a.FName =b.FName  and a.px =b.px 

Answer 5

Create Table T1(
    FName varchar(80)
)
Create Table T2(
    FName varchar(80)
)Insert into T1
    Select 'A'
Insert into T1
    Select 'A'
Insert into T1
    Select 'A'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T1
    Select 'B'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'A'
Insert into T2
    Select 'B'
Insert into T2
    Select 'B'
Insert into T2
    Select 'B'select id=identity(int,1,1),* into #t1 from t1
select id=identity(int,1,1),* into #t2 from t2select 
  a.fname as fname1,b.fname as fname2
from
(
select fname,px=(select count(1) from #t1 where fname=t.fname and ID<T.ID) from #t1 t
)a
full join
(
select fname,px=(select count(1) from #t2 where fname=t.fname and ID<T.ID) from #t2 t
)b
on 
  a.FName =b.FName  and a.px =b.px drop table t1,t2,#t1,#t2/*fname1                                                                           fname2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A                                                                                A
A                                                                                A
A                                                                                A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
NULL                                                                             A
B                                                                                B
B                                                                                B
B                                                                                B
B                                                                                NULL
B                                                                                NULL(13 行受影响)
*/