A 表
月 数据1
11 aa
1 bb
2 cc
11 ee
11 ff
b 表
月 数据2
11 tom
11 join
3 45
4 56
现是结果
月 数据1 数据2
11 aa tom
11 ee join
11 ff null
1 bb null
2 cc null
3 null 45
4 null 56
月 数据1
11 aa
1 bb
2 cc
11 ee
11 ff
b 表
月 数据2
11 tom
11 join
3 45
4 56
现是结果
月 数据1 数据2
11 aa tom
11 ee join
11 ff null
1 bb null
2 cc null
3 null 45
4 null 56
isnull(a.月,b.月) as 月,a.数据1,b.数据2
from
a full join b
on
a.月=b.月
11 ee join为啥 11 aa 这个对应的是tom 而不是 join
;with ach as
(
select *,px=row_number() over (partition by mm order by getdate())
from tb1
),art as
(
select *,px=row_number() over (partition by mm order by getdate())
from tb2
)select isnull(a.mm,b.mm) as mm,a.[value] as v1,b.[value] as v2
from ach a full join art b on a.mm=b.mm and a.px = b.px
if object_id('tempdb.dbo.#ta') is not null drop table #ta
create table #ta([月] int,[数据1] varchar(2))
insert #ta
select 11,'aa' union all
select 1,'bb' union all
select 2,'cc' union all
select 11,'ee' union all
select 11,'ff'--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([月] varchar(2),[数据2] varchar(5))
insert #tb
select '11','tom' union all
select '11','join' union all
select '3','45' union all
select '4','56'select isnull(a.[月],b.[月]) as [月],
[数据1],
b.[数据2]
from
(
select *,id=row_number() over(partition by [月]order by [数据1]) from #ta
)a
full join
(
select *,id=row_number() over(partition by [月]order by [数据2]) from #tb
)b
on a.[月]=b.[月] and a.id=b.id
order by [月]/*
月 数据1 数据2
----------- ---- -----
1 bb NULL
2 cc NULL
3 NULL 45
4 NULL 56
11 aa join
11 ee tom
11 ff NULL*/
if object_id('tempdb.dbo.#ta') is not null drop table #ta
create table #ta([月] int,[数据1] varchar(2))
insert #ta
select 11,'aa' union all
select 1,'bb' union all
select 2,'cc' union all
select 11,'ee' union all
select 11,'ff'--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([月] varchar(2),[数据2] varchar(5))
insert #tb
select '11','tom' union all
select '11','join' union all
select '3','45' union all
select '4','56'select isnull(a.[月],b.[月]) as [月],
[数据1],
b.[数据2]
from
(
select *,id=row_number() over(partition by [月]order by getdate()) from #ta
)a
full join
(
select *,id=row_number() over(partition by [月]order by getdate()) from #tb
)b
on a.[月]=b.[月] and a.id=b.id
order by [月]/*
月 数据1 数据2
----------- ---- -----
1 bb NULL
2 cc NULL
3 NULL 45
4 NULL 56
11 ee tom
11 ff join
11 aa NULL(7 行受影响)*/