如何从选择查询中联接两个别名表

Question

我是MSSQL的新手，我正在尝试加入从SELECT Query生成的两个别名表，就像这个一样

SELECT COUNT(T1.A1) AS [count],T1.A2 AS [Cname] FROM (
SELECT A1.Column1,A1.Column2
FROM A1
) T1
GROUP By T1.A1

我还有一张类似的桌子

SELECT COUNT(T2.A2) AS [count],T2.A2 AS [Cname] FROM (
SELECT A2.Column1,A2.Column2
FROM A2
) T2
GROUP By T2.A2

我想要这样的结果：

|   Cname  |T1.Count  | T2.Count |
|Column1   |   7      |  7       |
|Column2   |   9      |  7       |

我尝试过一些JOIN，但得到了无效对象T1。有人能建议我如何得到我的结果表吗？任何小的帮助都很感激。

Answer 1

您尝试过UNION ALL吗？类似这样的东西：

SELECT COUNT(a.C2), a.C1
FROM (
SELECT A1.Column1 as C1, A1.Column2 as C2
FROM A1
UNION ALL 
SELECT A2.Column1 as C1, A2.Column2 as C2
FROM A2) a
GROUP BY a.C1