带日/周/月频率参数的存储过程WHERE子句CASE语句优化问询

问题分析 #

你的存储过程中WHERE子句的CASE写法存在语法错误：CASE语句在WHERE中必须返回可用于判断的布尔等价值（如1/0），不能直接嵌入条件表达式；同时DAILY分支的CAST(GETDATE())缺少目标数据类型，导致语法不完整。

优化实现方案 #

方案一（推荐，索引友好）

用OR组合不同频率的条件分支，查询优化器能更好地利用Date01字段上的索引，提升查询性能：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Assemblies]
    @FREQUENCY varchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT SC01 AS 'Part Number', 
    (CASE 
        WHEN SC01 LIKE '%Assembly1%' THEN 'A1' 
        WHEN SC01 LIKE '%Assembly2%' THEN 'A2' 
        WHEN SC01 LIKE '%Assembly3%' THEN 'A3' 
        ELSE 'Other'
     END) AS 'Assembly', 
    (CASE 
        WHEN SC01 LIKE '%Component1%' THEN 'C1' 
        WHEN SC01 LIKE '%Component2%' THEN 'C2' 
        WHEN SC01 LIKE '%Component3%' THEN 'C3' 
        ELSE 'Other' 
    END) AS 'Component', 
    Key3 AS 'Group' 
FROM dbo.Part 
WHERE (SC01 LIKE '%Assembly%' OR SC01 LIKE '%Component%') 
AND (
    (@FREQUENCY = 'DAILY' AND CAST(Date01 AS DATE) = CAST(GETDATE() AS DATE))
    OR 
    (@FREQUENCY = 'WEEKLY' AND Date01 >= DATEADD(DAY, 1-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) 
                          AND Date01 <= DATEADD(DAY, 8-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)))
    OR 
    (@FREQUENCY = 'MONTHLY' AND Date01 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 
                           AND Date01 <= GETDATE())
)
ORDER BY SC01
END

方案二（修正CASE写法）

如果坚持使用CASE，需要通过嵌套CASE返回1/0来表示条件是否满足：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Assemblies]
    @FREQUENCY varchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT SC01 AS 'Part Number', 
    (CASE 
        WHEN SC01 LIKE '%Assembly1%' THEN 'A1' 
        WHEN SC01 LIKE '%Assembly2%' THEN 'A2' 
        WHEN SC01 LIKE '%Assembly3%' THEN 'A3' 
        ELSE 'Other'
     END) AS 'Assembly', 
    (CASE 
        WHEN SC01 LIKE '%Component1%' THEN 'C1' 
        WHEN SC01 LIKE '%Component2%' THEN 'C2' 
        WHEN SC01 LIKE '%Component3%' THEN 'C3' 
        ELSE 'Other' 
    END) AS 'Component', 
    Key3 AS 'Group' 
FROM dbo.Part 
WHERE (SC01 LIKE '%Assembly%' OR SC01 LIKE '%Component%') 
AND CASE 
        WHEN @FREQUENCY = 'DAILY' THEN 
            CASE WHEN CAST(Date01 AS DATE) = CAST(GETDATE() AS DATE) THEN 1 ELSE 0 END
        WHEN @FREQUENCY = 'WEEKLY' THEN 
            CASE WHEN Date01 >= DATEADD(DAY, 1-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) 
                 AND Date01 <= DATEADD(DAY, 8-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) THEN 1 ELSE 0 END
        WHEN @FREQUENCY = 'MONTHLY' THEN
            CASE WHEN Date01 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND Date01 <= GETDATE() THEN 1 ELSE 0 END
        ELSE 0 -- 处理无效频率参数
    END = 1
ORDER BY SC01
END

额外注意事项 #

• 建议在存储过程开头添加参数校验，比如判断@FREQUENCY是否为有效值（'DAILY'/'WEEKLY'/'MONTHLY'），避免无效查询。
• 如果Date01是datetime类型，转换为DATE类型可以忽略时间部分，确保只匹配日期维度的数据。
• 周计算逻辑中的GETDATE()-4是用于调整周起始日的偏移量，可根据业务实际的周起始规则修改。

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