表1
No,value
1,v1
1,v2
1,v3表2
No,value
2,v11
2,v22表3
No,value
1,v111
2,v222根据No合并起来成表1No,表1No,表2No,表2No,表3No,表3No
1,v1 ,null,null,1,v111
1,v2,null,null,null,null
1,v3,null,null,null,null
null,null,2,v11,2,v222
null,null,2,v22,null,null
No,value
1,v1
1,v2
1,v3表2
No,value
2,v11
2,v22表3
No,value
1,v111
2,v222根据No合并起来成表1No,表1No,表2No,表2No,表3No,表3No
1,v1 ,null,null,1,v111
1,v2,null,null,null,null
1,v3,null,null,null,null
null,null,2,v11,2,v222
null,null,2,v22,null,null
select m1.No,m1.value, m2.No,m2.value,m3.No,m3.value from
(select t.* , px = (select count(1) from t1 where No = t.No and value < t.value) + 1 from t1 t) m1
full join
(select t.* , px = (select count(1) from t2 where No = t.No and value < t.value) + 1 from t2 t) m2
on m1.no = m2.no and m1.px = m2.px full join
(select t.* , px = (select count(1) from t3 where No = t.No and value < t.value) + 1 from t3 t) m3
on isnull(m1.no , m2.no) = m3.no and isnull(m1.px , m2.px) = m3.px--sql 2005,使用row_number
select m1.No,m1.value, m2.No,m2.value,m3.No,m3.value from
(select t.* , px = row_number() over(partition by no order by value) from t1 t) m1
full join
(select t.* , px = row_number() over(partition by no order by value) from t2 t) m2
on m1.no = m2.no and m1.px = m2.px full join
(select t.* , px = row_number() over(partition by no order by value) from t3 t) m3
on isnull(m1.no , m2.no) = m3.no and isnull(m1.px , m2.px) = m3.px
go
if object_id('test.dbo.表1') is not null drop table 表1
-- 创建数据表
create table 表1
(
No int,
value char(8)
)
go
--插入测试数据
insert into 表1 select 1,'v1'
union all select 1,'v2'
union all select 2,'v3'
go
if object_id('test.dbo.表2') is not null drop table 表2
-- 创建数据表
create table 表2
(
No int,
value char(8)
)
go
--插入测试数据
insert into 表2 select 2,'v11'
union all select 2,'v22'
goif object_id('test.dbo.表3') is not null drop table 表3
-- 创建数据表
create table 表3
(
No int,
value char(8)
)
go
--插入测试数据
insert into 表3 select 1,'v111'
union all select 2,'v222'
go
--代码实现select a.No,a.value,b.No,b.value,c.No,c.value
from (select idd=row_number()over(partition by No order by getdate()),* from 表1)a
full join (select idd=row_number()over(partition by No order by getdate()),* from 表2)b on a.idd=b.idd and a.No=b.No
full join (select idd=row_number()over(partition by No order by getdate()),* from 表3)c on a.idd=c.idd and a.No=c.No
order by b.No,a.No,c.No
/*测试结果No value3 No value No value
-------------------------------------------------------------------------------------
1 v2 NULL NULL NULL NULL
1 v1 NULL NULL 1 v111
NULL NULL 2 v22 NULL NULL
2 v3 2 v11 2 v222 (4 行受影响)
*/
SELECT * FROM tb1
FULL JOIN tb2 ON tb1.no=tb2.no
FULL JOIN tb3 ON tb1.no=tb3.nono VALUE no VALUE no VALUE
----------- -------------------- ----------- -------------------- ----------- -----
1 v1 NULL NULL 1 v111
1 v2 NULL NULL 1 v111
1 v3 NULL NULL 1 v111
NULL NULL 2 v11 NULL NULL
NULL NULL 2 v22 NULL NULL
NULL NULL NULL NULL 2 v222(6 row(s) affected)
create table t1
(
No int,
value char(8)
)
go
--插入测试数据
insert into t1 select 1,'v1'
union all select 1,'v2'
union all select 2,'v3'
go
if object_id('test.dbo.表2') is not null drop table t2
-- 创建数据表
create table t2
(
No int,
value char(8)
)
go
--插入测试数据
insert into t2 select 2,'v11'
union all select 2,'v22'
goif object_id('test.dbo.表3') is not null drop table t3
-- 创建数据表
create table t3
(
No int,
value char(8)
)
go
--插入测试数据
insert into t3 select 1,'v111'
union all select 2,'v222'
go
select m1.No,m1.value, m2.No,m2.value,m3.No,m3.value from
(select t.* , px = (select count(1) from t1 where No = t.No and value < t.value) + 1 from t1 t) m1
full join
(select t.* , px = (select count(1) from t2 where No = t.No and value < t.value) + 1 from t2 t) m2
on m1.no = m2.no and m1.px = m2.px full join
(select t.* , px = (select count(1) from t3 where No = t.No and value < t.value) + 1 from t3 t) m3
on isnull(m1.no , m2.no) = m3.no and isnull(m1.px , m2.px) = m3.px
order by m1.no , m1.valuedrop table t1 , t2 , t3/*
No value No value No value
----------- -------- ----------- -------- ----------- --------
NULL NULL 2 v22 NULL NULL
1 v1 NULL NULL 1 v111
1 v2 NULL NULL NULL NULL
2 v3 2 v11 2 v222 (所影响的行数为 4 行)
*/