又是两表合并问题

[jimo_lang]

表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


意思是两表就是左右合并起来，不用对应

[dawugui]

--sql 2000

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

[dawugui]

--sql 2005

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

[喜-喜]

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 行受影响)
*/

[「已注销」]

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

[ws_hgo]

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 行受影响)

[就是just4]

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
*/