mysql - 在 Microsoft Access 中使用 MySQL 查询

Question

亲爱的，

不用说，我一直在胡思乱想，想将一个在 MySQL 中运行良好的查询转换到 Microsoft Access 中。我得到了一些异常(exception)。

这是一个比较查询，应该确实比较当年和上一年同月的电力消耗，按建筑物分组。

正如我所说，它在 MySQL 中工作得很好，但在 MS Access 中却不行。

原始查询:

SELECT
tblElectricReadings.Building,
SUM(CASE WHEN tblElectricReadings.ReadingDate >= DATE_SUB(NOW(), INTERVAL 1 YEAR) THEN (tblElectricReadings.EndMeter - tblElectricReadings.StartMeter) ELSE NULL END) AS 'Cons1',
SUM(CASE WHEN tblElectricReadings.ReadingDate BETWEEN DATE_SUB(NOW(), INTERVAL 2 YEAR) AND DATE_SUB(NOW(), INTERVAL 1 YEAR) THEN (tblElectricReadings.EndMeter - tblElectricReadings.StartMeter) ELSE NULL END) AS 'Cons2',
CONCAT(MONTHNAME(tblElectricReadings.ReadingDate)) AS MonthRecorded 
FROM tblElectricReadings
WHERE   tblElectricReadings.ReadingDate >= DATE_SUB(NOW(), INTERVAL 2 YEAR)
GROUP BY tblElectricReadings.Building,
CONCAT(MONTHNAME(tblElectricReadings.ReadingDate))
ORDER BY tblElectricReadings.ReadingDate, tblElectricReadings.Building

我意识到 MS Access SQL 有点不同，所以我在上网后尝试了一些变体。 这是我所能得到的最接近的结果，但它仍然行不通:

SELECT
tblElectricReadings.[Building],
SUM(IIF(tblElectricReadings.[Reading Date] >= DATEADD("y",-1,NOW()), (tblElectricReadings.[End Meter] - tblElectricReadings.[Start Meter]),0)) AS 'Cons1',
SUM(IIF(tblElectricReadings.[Reading Date] BETWEEN DATEADD("y",-2,NOW()) AND DATEADD("y",-1,NOW()), (tblElectricReadings.[End Meter] - tblElectricReadings.[Start Meter]),0)) AS 'Cons2',
MONTHNAME(tblElectricReadings.[Reading Date]) AS MonthRecorded
FROM tblElectricReadings
WHERE tblElectricReadings.[Reading Date] >= DATEADD("y",-2,NOW())
GROUP BY tblElectricReadings.[Building], MONTHNAME(tblElectricReadings.[Reading Date])
ORDER BY tblElectricReadings.[Reading Date], tblElectricReadings.[Building]

有了上面的内容，我现在收到以下错误:“您的查询不包括指定的表达式 'tblElectricReadings.[Reading Date]' 作为聚合函数的一部分。

有没有人知道我做错了什么？任何帮助当然将不胜感激 :) 如果我问的问题很简单，我深表歉意，但我仍然在这里学到很多东西 :(

感谢您提供的任何最终帮助。

编辑 3:我成功了!这对我有用 - 感谢大家提供的帮助，不胜感激。

SELECT 
tblElectricReadings.[Building], 
MONTHNAME(MONTH(tblElectricReadings.[Reading Date])) AS MonthRecorded, 
SUM(IIF(tblElectricReadings.[Reading Date] >= DATEADD("yyyy",-1,NOW()), (tblElectricReadings.[End Meter] - tblElectricReadings.[Start Meter]),0)) AS [Current Year Consumption], 
SUM(IIF(tblElectricReadings.[Reading Date] BETWEEN DATEADD("yyyy",-2,NOW()) AND DATEADD("yyyy",-1,NOW()), (tblElectricReadings.[End Meter] - tblElectricReadings.[Start Meter]),0)) AS [Previous Year Consumption]
FROM tblElectricReadings
WHERE tblElectricReadings.[Reading Date] >= DATEADD("yyyy",-2,NOW())
GROUP BY tblElectricReadings.[Building], MONTH(tblElectricReadings.[Reading Date])
ORDER BY MONTH(tblElectricReadings.[Reading Date]), tblElectricReadings.[Building];

Answer 1

不确定这是唯一的问题但是

SUM(IIF(tblElectricReadings.ReadingDate >= DATEADD("y",-1,NOW()), (tblElectricReadings.EndMeter - tblElectricReadings.StartMeter),"")

显然是错误的:你不能对 Strings 求和，即使是空的。试试看:

SUM(IIF(tblElectricReadings.ReadingDate >= DATEADD("y",-1,NOW()), (tblElectricReadings.EndMeter - tblElectricReadings.StartMeter),0)

对其他事件执行相同的操作。

编辑:

我还认为您的 ORDER BY 子句没有意义，因为它试图对输出数据集中不存在的内容进行排序。应该是:

ORDER BY MONTHNAME(tblElectricReadings.ReadingDate), tblElectricReadings.Building