SQL嵌套查询OR语法报错及AS别名问题排查求助

核心错误点 #

1. 括号逻辑混乱：OR连接的跨年份条件未加外层括号，还存在冗余括号。由于AND优先级高于OR，不包裹的话逻辑会偏离预期，直接触发语法报错。
2. AS关键字前缺失空格：SUM(sold)+sum(Issued)AS Expr1 中AS前没有空格，SQL引擎无法识别这是别名定义，因此报「关键字'as'附近有语法错误」。
3. 别名下划线无问题：SQL允许下划线作为标识符的一部分，不是报错原因。

修正后的代码片段 #

-- 12个月使用量统计（修正版）
(SELECT SUM(sold) + SUM(Issued) AS Expr1
 FROM dbo.M_NewtrnYM 
 WHERE WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse 
   AND ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber
   -- 用括号包裹OR两边的年份月份条件，确保逻辑正确，同时修正括号数量
   AND (
       (YEAR = YEAR(CURRENT_TIMESTAMP) - 1 AND CAST([Month] AS Integer) >= MONTH(CURRENT_TIMESTAMP))
       OR 
       (YEAR = YEAR(CURRENT_TIMESTAMP) AND CAST([Month] AS Integer) <= MONTH(CURRENT_TIMESTAMP))
   )) AS Twelvemth_usage,

-- 6个月使用量统计（修正版）
(SELECT SUM(sold) + SUM(Issued) AS Expr1
 FROM dbo.M_NewtrnYM 
 WHERE WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse 
   AND ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber
   AND (
       (YEAR = YEAR(DATEADD(m, -6, GETDATE() - DATEPART(d, GETDATE()) + 1)) 
        AND CAST([Month] AS Integer) >= MONTH(DATEADD(m, -6, GETDATE() - DATEPART(d, GETDATE()) + 1)))
       OR 
       (YEAR = YEAR(CURRENT_TIMESTAMP) AND CAST([Month] AS Integer) <= MONTH(CURRENT_TIMESTAMP))
   )) AS sixmth_usage,

额外优化建议 #

• 统一函数名大小写（如SUM/MONTH），提升代码可读性。
• 可将时间范围计算逻辑提取为变量，避免重复计算，简化代码：

DECLARE @TwelveMthStartYear INT = YEAR(CURRENT_TIMESTAMP) - 1;
DECLARE @TwelveMthStartMonth INT = MONTH(CURRENT_TIMESTAMP);
DECLARE @SixMthStartDate DATE = DATEADD(m, -6, GETDATE() - DATEPART(d, GETDATE()) + 1);
DECLARE @SixMthStartYear INT = YEAR(@SixMthStartDate);
DECLARE @SixMthStartMonth INT = MONTH(@SixMthStartDate);

后续子查询直接使用这些变量即可，减少冗余代码与计算开销。

内容的提问来源于stack exchange，提问作者axsprog