表1
字段1,字段2
11,21
12,22
13,23
表2
字段1,字段2
a1,b1
a2,b2
a3,b3
a4,b4合成
11,21,a1,b1
12,22,a2,b2
13,23,a3,b3
null,null,a4,b4
意思是两表就是左右合并起来,不用对应
字段1,字段2
11,21
12,22
13,23
表2
字段1,字段2
a1,b1
a2,b2
a3,b3
a4,b4合成
11,21,a1,b1
12,22,a2,b2
13,23,a3,b3
null,null,a4,b4
意思是两表就是左右合并起来,不用对应
select m.字段1,m.字段2,n.字段1,n.字段2 from
(select t.* ,px=(select count(1) from t1 where 字段1 < t.字段1 or (字段1 = t.字段1 and 字段2<t.字段2)) + 1 from t1 t) m
full join
(select t.* ,px=(select count(1) from t2 where 字段1 < t.字段1 or (字段1 = t.字段1 and 字段2<t.字段2)) + 1 from t2 t) n
on m.px = n.px
select m.字段1,m.字段2,n.字段1,n.字段2 from
(select t.* ,px=row_number() over(order by 字段1,字段2) from t1 t) m
full join
(select t.* ,px=row_number() over(order by 字段1,字段2) from t2 t) n
on m.px = n.px
select a.* ,row_number()over(order by getdate()) row
from a
full join
(
select b.* ,row_number()over(order by getdate()) row
from b) m
on m.row=a.row
go
if object_id('test.dbo.表1') is not null drop table 表1
-- 创建数据表
create table 表1
(
字段1 char(3),
字段2 char(3)
)
go
--插入测试数据
insert into 表1 select '11','21'
union all select '12','22'
union all select '13','23'
go
if object_id('test.dbo.表2') is not null drop table 表2
-- 创建数据表
create table 表2
(
字段1 char(3),
字段2 char(3)
)
go
--插入测试数据
insert into 表2 select 'a1','b1'
union all select 'a2','b2'
union all select 'a3','b3'
union all select 'a4','b4'
go
--代码实现select a.字段1,a.字段2,b.字段1,b.字段2
from (select idd=row_number()over(order by getdate()),* from 表1)a
full join (select idd=row_number()over(order by getdate()),* from 表2)b on a.idd=b.idd
/*测试结果字段1 字段2 字段1 字段2
---------------------
11 21 a1 b1
12 22 a2 b2
13 23 a3 b3
NULL NULL a4 b4 (4 行受影响)
*/
if object_id('表1') is not null drop table 表1
create table 表1
(
字段1 int,
字段2 int
)
insert into 表1 select 11,21
union all select 12,22
union all select 13,23if object_id('表2') is not null drop table 表2
create table 表2
(
字段1 varchar(20),
字段2 varchar(20)
)
insert into 表2 select 'a1','b1'
union all select 'a2','b2'
union all select 'a3','b3'
union all select 'a4','b4'select t.字段1,t.字段2,t1.字段1,t1.字段2
from
(
select *,
(select count(*)+1 from 表1 where 字段1<t.字段1) row from 表1 t
)
t
full join
(
select *,
(select count(*)+1 from 表2 where 字段1<t.字段1) row from 表2 t
) t1
on t.row=t1.row字段1 字段2 字段1 字段2
----------- ----------- -------------------- --------------------
11 21 a1 b1
12 22 a2 b2
13 23 a3 b3
NULL NULL a4 b4(4 行受影响)
DECLARE @test2 TABLE(s1 VARCHAR(20),s2 VARCHAR(20))INSERT INTO @test1(f1,f2)
SELECT 11,21 UNION ALL
SELECT 12,22 UNION ALL
SELECT 13,23INSERT INTO @test2(s1,s2)
SELECT 'a1','b1' UNION ALL
SELECT 'a2','b2' UNION ALL
SELECT 'a3','b3' UNION ALL
SELECT 'a4','b4'IF OBJECT_ID('tempdb..#1') IS NOT NULL DROP TABLE tempdb..#1
SELECT IDENTITY(INT,1,1) id,t1.f1,t1.f2 INTO #1 FROM @test1 AS t1
IF OBJECT_ID('tempdb..#2') IS NOT NULL DROP TABLE tempdb..#2
SELECT IDENTITY(INT,1,1) id,t2.s1,t2.s2 INTO #2 FROM @test2 AS t2SELECT t1.f1,t1.f2,t2.s1,t2.s2 FROM #1 AS t1 FULL OUTER JOIN #2 AS t2 ON t1.id = t2.idDROP TABLE tempdb..#1
DROP TABLE tempdb..#2
/*
f1 f2 s1 s2
----------- ----------- -------------------- --------------------
11 21 a1 b1
12 22 a2 b2
13 23 a3 b3
NULL NULL a4 b4
*/