时间戳

首页 > 解决方案 > Excel VBA SQL UNION SUM 对具有不同列名的表进行 GROUP BY

sql - Excel VBA SQL UNION SUM 对具有不同列名的表进行 GROUP BY

问题描述

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

表订单： 到期日、付款、汇率

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

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

其中两个表在日期 + 月份是 UNIONED ALL，而 Total 是两个表中所有行的总和。

我的伪 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 上失败了，因为使用 ALIASES（错误说不在聚合函数中使用 YEAR(ORDREC.[Due Date])），但我必须使用别名，因为两个表中的列名不同。关于如何在单个 SQL 查询中实现这一点的任何建议？

Windows 10 上的 Excel 2016 提供程序=Microsoft.Jet.OLEDB.4.0；

标签： sqlexcelvbagroup-byunion-all

解决方案

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

您在查询中使用SUM聚合函数，需要在两个查询中的子句中UNION ALL添加非聚合列。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

推荐阅读