我认为这将是一个相当直接的过程,但我有问题合并/合并三个表在一起。每个prvs_
和cur_
前缀大约有20个左右的属性,但我选择只为问题添加几个。
表1,#no_change_or_transfer
看起来像这样,大约有100万行:
prvs_loan_num | prvs_wac | prvs_tni | cur_loan_num | cur_wac | cur_tni
---------------|-----------|-----------|---------------|----------|---------
86 | 2.3 | 2000 | 86 | 2.5 | 2000
表2,#left_in_aug
看起来像这样,大约有20,000行:
prvs_loan_num | prvs_wac | prvs_tni
---------------|-----------|-----------
10 | 3.1 | 1500
表3,#joined_in_sept
看起来像这样,大约有20,000行:
cur_loan_num | cur_wac | cur_tni
---------------|-----------|-----------
22 | 3.5 | 1700
我希望这个表是这样的:
prvs_loan_num | prvs_wac | prvs_tni | cur_loan_num | cur_wac | cur_tni
---------------|-----------|-----------|---------------|----------|---------
86 | 2.3 | 2000 | 86 | 2.5 | 2000
10 | 3.1 | 1500 | NULL | NULL | NULL
NULL | NULL | NULL | 22 | 3.5 | 1700
我已经尝试过这样的full outer join
,但收到一个错误,说每个表中的列名必须是唯一的:
SELECT *
INTO #month_over_month
FROM #no_change_or_transfer a
FULL OUTER JOIN #left_in_aug b
ON a.prvs_loan_number = b.prvs_loan_number
FULL OUTER JOIN #joined_in_sept c
ON a.cur_loan_number = c.cur_loan_number
我也尝试了这个,并收到一个错误,说所有使用UNION的查询必须在其目标列表中具有相同数量的表达式:
SELECT x.*
INTO #month_over_month
FROM (SELECT * FROM #no_change_or_transfer
UNION
SELECT * FROM #left_in_aug
UNION
SELECT * FROM #joined_in_sept) x
您可以使用UNION ALL
合并表。例如:
select prvs_loan_num, prvs_wac, prvs_tni, cur_loan_num, cur_wac, cur_tni
from #no_change_or_transfer
union all
select prvs_loan_num, prvs_wac, prvs_tni, null, null, null
from #left_in_aug
union all
select null, null, null, cur_loan_num, cur_wac, cur_tni
from #joined_in_sept
联合中的所有列必须在每个查询中匹配。
显式指定列名,必要时提供null
。
请注意,列名仅由第一个查询决定。
select prvs_loan_num, prvs_wac, prvs_tni, cur_loan_num, cur_wac, cur_tni
from #no_change_or_transfer
union all
select prvs_loan_num, prvs_wac, prvs_tni, null, null, null
from #left_in_aug
union all
select null, null, null, cur_loan_num, cur_wac, cur_tni
from #joined_in_sept