Excel VBA SQL UNION SUM 与具有不同列名的表上的分组依据

Question

我有两个表，列名不同，如下所示：

表顺序：到期日、付款、汇率

表发票：日期、小计、汇率

我想要以下结果表：年、月、总计

其中两个表在日期 + 月份上联合所有，总计是两个表中所有行的总和。

我的伪SQL看起来像这样：

SELECT YearID, MonthID, SUM(Amount) FROM (
SELECT YEAR(ORDREC.[Due Date]) as YearID, MONTH(ORDREC.[Due Date]) as MonthID, SUM(ORDREC.[Pay] * ORDREC.[Exchange Rate]) as Amount
FROM orders-table AS ORDREC
WHERE ...
UNION ALL
SELECT YEAR(INV.[Date]) as YearID, MONTH(INV.[Date]) as MonthID, SUM(INV.[Subtotal] * INV.[Exchange Rate]) as Amount
FROM invoices-table AS INV
WHERE ...
) x GROUP BY YearID, MonthID

我在 GROUP BY 上失败了，因为使用了别名(错误说不使用 YEAR(ORDREC.[到期日]((，但我必须使用别名，因为两个表中的列名不同。关于如何在单个SQL查询中实现此目的的任何建议？

Excel 2016 on Windows 10 提供程序=Microsoft.Jet.OLEDB.4.0;

Answer 1

使用聚合函数时，需要在子句中添加非聚合列GROUP BY。

您在UNION ALL查询中使用SUM聚合函数，需要在两个查询GROUP BY子句中添加非聚合列。

SELECT YearID, MonthID, SUM(Amount) FROM (
SELECT YEAR(ORDREC.[Due Date]) as YearID, MONTH(ORDREC.[Due Date]) as MonthID, SUM(ORDREC.[Pay] * ORDREC.[Exchange Rate]) as Amount
FROM orders-table AS ORDREC
WHERE ...
GROUP BY YEAR(ORDREC.[Due Date]),MONTH(ORDREC.[Due Date])
UNION ALL
SELECT YEAR(INV.[Date]) as YearID, MONTH(INV.[Date]) as MonthID, SUM(INV.[Subtotal] * INV.[Exchange Rate]) as Amount
FROM invoices-table AS INV
WHERE ...
GROUP BY YEAR(INV.[Date]),MONTH(INV.[Date])
) x GROUP BY YearID, MonthID

如果我理解正确，您可以使用它然后执行SUM聚合函数，我认为错误会消失。

SELECT YearID, MonthID, SUM(Amount) FROM (
SELECT YEAR(ORDREC.[Due Date]) as YearID, MONTH(ORDREC.[Due Date]) as MonthID, (ORDREC.[Pay] * ORDREC.[Exchange Rate]) as Amount
FROM orders-table AS ORDREC
WHERE ...
UNION ALL
SELECT YEAR(INV.[Date]) as YearID, MONTH(INV.[Date]) as MonthID, (INV.[Subtotal] * INV.[Exchange Rate]) as Amount
FROM invoices-table AS INV
WHERE ...
) x GROUP BY YearID, MonthID