又是两表合并问题

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