標題: [發問] MS SQL full join問題 [打印本頁] 作者: symis 時間: 2017-1-23 14:23 標題: MS SQL full join問題
--MS SQL full join問題
declare @t1 table (f1 nvarchar(50), f2 nvarchar(50))
declare @t2 table (f1 nvarchar(50), f3 nvarchar(50))
declare @t3 table (f1 nvarchar(50), f4 nvarchar(50))
insert into @t1 (f1,f2) values ('a','1')
insert into @t1 (f1,f2) values ('c','2')
insert into @t1 (f1,f2) values ('d','3')
insert into @t2 (f1,f3) values ('a','4')
insert into @t2 (f1,f3) values ('b','5')
insert into @t2 (f1,f3) values ('d','6')
insert into @t3 (f1,f4) values ('c','7')
insert into @t3 (f1,f4) values ('e','8')
--select t1f1=t1.f1,t2f1=t2.f1,t3f1=t3.f1,f2,f3,f4
select f1=isnull(t1.f1,isnull(t2.f1,t3.f1)),f2,f3,f4
from @t1 t1
full join @t2 t2 on t1.f1=t2.f1
full join @t3 t3 on t2.f1=t3.f1
order by f1
上列結果如圖1,f1的值有'c'重複
如何能有圖2的結果? (f1不要重複)
我知道改成
select f1=isnull(t1.f1,isnull(t2.f1,t3.f1)),f2,f3,f4
from @t1 t1
full join @t2 t2 on t1.f1=t2.f1
full join @t3 t3 on t1.f1=t3.f1
order by f1
能達到目的,但這是巧合,不保險,
因為實務上各table值很多,無法一一去檢查有哪些會沒對應到
請問有沒有治本之道?
