我有一个包含12个表的数据库,其中包含2008年1月至12月的员工养老金详细信息.我想获得每个员工12个月的总养老金,从所有12个表(jan到dec)中累计金额.
我该如何编写查询?
您可能需要重新考虑此设计,而是使用一个表格来指示MONTH和YEAR.
鉴于您当前的设计,您的查询将需要看起来像这样(显然替换您自己的表和字段名称):
首先,创建此查询并将其另存为"Monthly Totals":
SELECT EmployeeId AS EmployeeNumber, SUM(Pension_Amount) AS Pension_Totals FROM Pension_January GROUP BY EmployeeId UNION ALL SELECT EmployeeId, SUM(Pension_Amount) FROM Pension_February GROUP BY EmployeeId UNION ALL .....Other months..... UNION ALL SELECT EmployeeId, SUM(Pension_Amount) FROM Pension_December GROUP BY EmployeeId;
其次,创建另一个引用第一个的查询:
SELECT [Monthly Totals].EmployeeNumber, SUM([Monthly Totals].Pension_Totals) AS Employee_Total FROM [Monthly Totals] GROUP BY [Monthly Totals].EmployeeNumber;
现在保存第二个查询并运行它.它应该给你你需要的东西.