SQL Server WHERE子句使用CASE语句过滤财季对应Date_Received数据问题

错误原因 #

CASE表达式仅支持返回单个标量值，你在CASE分支中直接返回BETWEEN条件片段的写法不符合SQL语法规范，且MONTH(Date_Received) = [多值范围]的逻辑本身也无法成立，因此会触发语法报错。

修复方案 #

方案1（推荐）：直接使用逻辑条件拼接 #

无需使用CASE表达式，直接通过OR拼接不同变量值对应的过滤规则，可读性更高、性能更优：

DECLARE @ReviewPeriodQuarter INT;

SELECT * FROM [Table]
WHERE
    (@ReviewPeriodQuarter = 1 AND MONTH(Date_Received) BETWEEN 10 AND 12)
    OR (@ReviewPeriodQuarter = 2 AND MONTH(Date_Received) BETWEEN 1 AND 3)
    OR (@ReviewPeriodQuarter = 3 AND MONTH(Date_Received) BETWEEN 4 AND 6)
    OR (@ReviewPeriodQuarter = 4 AND MONTH(Date_Received) BETWEEN 7 AND 9);

方案2：适配CASE表达式的写法 #

如果必须使用CASE实现，可以调整逻辑为让CASE返回匹配标识，再进行值匹配：

DECLARE @ReviewPeriodQuarter INT;

SELECT * FROM [Table]
WHERE 1 = CASE
    WHEN @ReviewPeriodQuarter = 1 AND MONTH(Date_Received) BETWEEN 10 AND 12 THEN 1
    WHEN @ReviewPeriodQuarter = 2 AND MONTH(Date_Received) BETWEEN 1 AND 3 THEN 1
    WHEN @ReviewPeriodQuarter = 3 AND MONTH(Date_Received) BETWEEN 4 AND 6 THEN 1
    WHEN @ReviewPeriodQuarter = 4 AND MONTH(Date_Received) BETWEEN 7 AND 9 THEN 1
    ELSE 0
END;

注意事项 #

原代码中的Table是SQL Server保留关键字，使用时需要包裹方括号[Table]，否则也可能触发语法错误。如果需要利用Date_Received字段的索引优化查询性能，可以进一步改写为避免使用MONTH()函数的范围过滤写法。

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