tsql - 加入相同数据的多个表并返回所有行

Question

更新:

我原来使用 FULL OUTER JOIN 的尝试没有正常工作。我已经更新了问题以反射(reflect)真正的问题。抱歉呈现经典XY PROBLEM .

---

我试图在一个查询中从多个表中检索一个数据集，该查询按数据的年、月分组。

最终结果应该是这样的:

| Year | Month | Col1 | Col2 | Col3 ||------+-------+------+------+------|| 2012 |   11  |  231 |   -  |  -   || 2012 |   12  |  534 |  12  |  13  || 2013 |   1   |   -  |  22  |  14  |

Coming from data that looks like this:

Table 1:

| Year | Month | Data ||------+-------+------|| 2012 |   11  |  231 || 2012 |   12  |  534 |

Table 2:

| Year | Month | Data ||------+-------+------|| 2012 |   12  |  12  || 2013 |   1   |  22  |

Table 3:

| Year | Month | Data ||------+-------+------|| 2012 |   12  |  13  || 2013 |   1   |  14  |

I tried using FULL OUTER JOIN but this doesn't quite work because in my SELECT clause because no matter which table I select 'Year' and 'Month' from there are null values.

SELECT 
 Collase(t1.year,t2.year,t3.year)
,Collese(t1.month,t2.month,t3.month)
,t1.data as col1
,t2.data as col2
,t3.data as col3
From t1
FULL OUTER JOIN t2
on t1.year = t2.year and t1.month = t2.month
FULL OUTER JOIN t3
on t1.year = t3.year and t1.month = t3.month

结果是这样的(太困惑了，无法准确重复我使用这个演示数据会得到什么):

| Year | Month | Col1 | Col2 | Col3 ||------+-------+------+------+------|| 2012 |   11  |  231 |   -  |  -   || 2012 |   12  |  534 |  12  |  13  || 2013 |   1   |   -  |  22  |      ||  -   |   1   |   -  |  -   |  14  |

Answer 1

如果您的数据允许(不是 100 列)，这通常是一种干净的方式:

select year, month, sum(col1) as col1, sum(col2) as col2, sum(col3) as col3
from (
    SELECT t1.year, t1.month, t1.data as col1, 0 as col2, 0 as col3
    From t1
    union all 
    SELECT t2.year, t2.month, 0 as col1, t2.data as col2, 0 as col3
    From t2
    union all 
    SELECT t3.year, t3.month, 0 as col1, 0 as col2, t3.data as col3
    From t3
) as data
group by year, month