对不起,标题可能会让人困惑,但我会尽量在这里更好地解释。所以我有两个表
Table1
Name
Int
Decimal
Table2
Name
Int
Decimal
我正试图将数据合并到第三个表中。它看起来像这样
Table3
Name
Table1_Int
Table1_Decimal
Table2_Int
Table2_Decimal
插入不是问题。问题是我有一些名字存在于一个表中而不存在于另一个表中。我仍然希望这些显示,只是NULL值没有值。
这是我的存储过程
IF NOT EXISTS(
SELECT Name FROM Table3)
INSERT INTO Table3(
Name,
Table1_Int,
Table1_Decimal,
Table2_Int,
Table2_Decimal)
SELECT
t.Name,
AVG(t.Int) as Table1_Int,
AVG(CAST(t.Decimal as decimal(6,2))) as Table1_Decimal
AVG(a.Int) as Table2_Int,
AVG(CAST(a.Decimal as decimal(6,2))) as Table2_Decimal
FROM Table1 t
JOIN Table2 a
ON t.Name = a.Name
GROUP BY t.Name
ELSE
UPDATE Table3
SET Name = Name
是否有任何方式我可以抓取所有的名称,无论他们之间的表匹配?
我将使用FULL OUTER JOIN的基本逻辑。我希望它看起来像这样;
SELECT
COALESCE(t.name, a.name) name
,AVG(t.Int) t_int
,AVG(CAST(t.Decimal as decimal(6,2))) t_decimal
,AVG(a.Int) a_int
,AVG(CAST(a.Decimal as decimal(6,2))) a_decimal
FROM Table1 t
FULL OUTER JOIN Table2 a
ON t.name = a.name
GROUP BY COALESCE(t.name, a.name)