22,300
社区成员




DECLARE @test1 TABLE(f1 INT,f2 int)
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,23
INSERT 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 t2
SELECT t1.f1,t1.f2,t2.s1,t2.s2 FROM #1 AS t1 FULL OUTER JOIN #2 AS t2 ON t1.id = t2.id
DROP 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
*/
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,23
if 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 行受影响)
try~
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
use test
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 行受影响)
*/
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 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