日期添加 关键字'AS'附近的语法不正确

Question

下面的查询在 DATEADD 部分中给出了错误，我似乎在这里看不到问题或语法错误。关于我的错误有什么提示吗？

SELECT 
YEAR(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime) AS DATE) AS fld_year, 
MONTH(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime) AS DATE) AS fld_month, 
fld_DeviceId, 
SUM(fld_EnergyTotalIndexValue) AS Total_Monthly_Consumption,
MIN(fld_ConsDateTime) AS Min_ConsDateTime, 
MAX(fld_ConsDateTime) AS Max_ConstDateTime
FROM tbl_EM_DeviceEnergyHourlyConsumption
WHERE
fld_Deleted = 0
AND fld_DeviceId = 5
AND fld_ConsDateTime >= DATEADD(MINUTE, -180 + 480, '2017-01-01 00:00')
AND fld_ConsDateTime <  DATEADD(MINUTE, -180 + 480, '2017-08-01 00:00')
GROUP BY YEAR(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime) AS DATE), MONTH(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime) AS DATE), fld_DeviceId
ORDER BY fld_month ASC

PS：错误消息：关键字"AS"附近的语法不正确。

Answer 1

你在4个不应该出现的地方AS DATE。这是更正后的版本：

SELECT 
YEAR(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime)) AS fld_year, 
MONTH(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime)) AS fld_month, 
fld_DeviceId, 
SUM(fld_EnergyTotalIndexValue) AS Total_Monthly_Consumption,
MIN(fld_ConsDateTime) AS Min_ConsDateTime, 
MAX(fld_ConsDateTime) AS Max_ConstDateTime
FROM tbl_EM_DeviceEnergyHourlyConsumption
WHERE
fld_Deleted = 0
AND fld_DeviceId = 5
AND fld_ConsDateTime >= DATEADD(MINUTE, -180 + 480, '2017-01-01 00:00')
AND fld_ConsDateTime <  DATEADD(MINUTE, -180 + 480, '2017-08-01 00:00')
GROUP BY YEAR(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime)), MONTH(DATEADD(MINUTE, 180 - 480, fld_ConsDateTime)), fld_DeviceId
ORDER BY fld_month ASC

Answer 2

GROUP BY 子句不允许使用别名。另外，不要将您的别名嵌套在 SELECT 中。请参阅下面的简化示例，说明您不能执行的操作。

DECLARE @Table TABLE (ID INT)
SELECT ID,COUNT(*) FROM @Table GROUP BY ID --Works
SELECT ID AS TEST,COUNT(*) FROM @Table GROUP BY ID --Works
SELECT (ID AS TEST) AS [ThisWillBreak],COUNT(*) FROM @Table GROUP BY ID
SELECT ID,COUNT(*) FROM @Table GROUP BY ID AS [ThisWillBreak]