我有以下两个表.我正在使用sql Server 2008 R2
Create table #tmp1 (
a char(1)
)
Create table #tmp2 (
id int,a char(1),val int
)
insert #tmp1 values ('A')
insert #tmp1 values ('B')
insert #tmp1 values ('C')
insert #tmp2 values (1,'A',10)
insert #tmp2 values (1,'B',20)
insert #tmp2 values (2,30)
insert #tmp2 values (2,'C',40)
select * from #tmp1 t1 left outer join #tmp2 t2 on t1.a = t2.a
order by t2.id
这将返回结果集
A 1 A 10 B 1 B 20 C 2 C 40 A 2 A 30
我想有以下结果集
A 1 A 10 B 1 B 20 C 1 null null A 2 A 30 B 2 null null C 2 C 40
现在我通过创建一个像这样的交叉连接的新表然后进行外连接来实现这一点
select * into #tmp3 from #tmp1 cross join (select distinct ID from #tmp2) t select * from #tmp3 t1 left outer join #tmp2 t2 on t1.a = t2.a and t1.id = t2.id
有一个更好的方法吗 ？
谢谢
解决方法
为了得到你想要的东西,你需要一个“驾驶”表.也就是说,您需要所有组合的完整列表,然后加入其他表以获取匹配项.这是一种方式：
select t1.a,t2.*
from (select t1.a as a,t2.id as id
from (select distinct a from #tmp1 t1) t1
cross join
(select distinct id from #tmp2 t2) t2
) driving left outer join
#tmp1 t1
on t1.a = driving.a left outer join
#tmp2 t2
on t2.id = driving.id and
t2.a = driving.a
order by t2.id
