34,588
社区成员
发帖
与我相关
我的任务
分享
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
........
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 t2
select
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 行受影响)
*/
select id=identity(int,1,1),* into #t1 from t1
select id=identity(int,1,1),* into #t2 from t2
select
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
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
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 行受影响)
*/