如何在SQL Server中按名称及多可选参数正确筛选商品实体

你的SQL筛选失效的核心问题是筛选条件逻辑错误：

• 第二个括号内用OR连接所有筛选条件，导致只要满足其中任意一个条件就会被选中，比如当@IsNew=0时，所有非新品的商品都会被纳入结果，完全忽略了分类ID、折扣的筛选要求。
• 参数默认值的处理逻辑不对，没有区分“不使用该筛选条件”和“筛选对应状态为0的商品”。

我们需要调整每个筛选条件的逻辑：仅当参数为有效筛选值时，才应用该条件；否则该条件自动失效（不参与筛选）。

修正后的SQL代码 #

DECLARE @Name       AS VARCHAR(100) = '',
        @CategoryId AS INT          = NULL,
        @IsNew      AS BIT          = 0,
        @IsDiscount AS BIT          = 0;

SELECT
    *
FROM
    Products P
WHERE
    -- 名称模糊匹配：空值或空字符串时匹配所有
    (P.Name COLLATE Latin1_General_CI_AI LIKE '%' + COALESCE(@Name, '') + '%')
    -- 分类ID筛选：仅当@CategoryId不为NULL时生效
    AND (@CategoryId IS NULL OR P.CategoryId = @CategoryId)
    -- 新品筛选：仅当@IsNew=1时生效（0表示不筛选新品）
    AND (@IsNew = 0 OR P.New = @IsNew)
    -- 折扣筛选：仅当@IsDiscount=1时生效（0表示不筛选折扣商品）
    AND (@IsDiscount = 0 OR IIF(P.PriceDiscount IS NULL, 0, 1) = @IsDiscount)

逻辑说明 #

• 名称筛选：保持原有逻辑，当@Name为空或NULL时，LIKE '%%'会匹配所有商品名称。
• 分类ID筛选：如果@CategoryId是NULL，该条件直接为TRUE，不参与筛选；如果传入了具体分类ID，则只匹配对应分类的商品。
• 新品筛选：如果@IsNew=0，表示不需要筛选新品，条件为TRUE；如果@IsNew=1，则只匹配标记为新品的商品。
• 折扣筛选：如果@IsDiscount=0，表示不需要筛选折扣商品，条件为TRUE；如果@IsDiscount=1，则只匹配有折扣（PriceDiscount不为NULL）的商品。

不同场景的测试验证 #

• 仅按名称筛选：设置@Name='手机'，其他参数保持默认，返回所有名称含“手机”的商品。
• 仅按分类筛选：设置@CategoryId=2，其他参数默认，返回分类ID为2的所有商品。
• 名称+新品筛选：设置@Name='手机'、@IsNew=1，返回名称含“手机”的新品商品。
• 仅折扣筛选：设置@IsDiscount=1，其他参数默认，返回所有有折扣的商品。
• 全条件筛选：设置@Name='手机'、@CategoryId=2、@IsNew=1、@IsDiscount=1，返回分类2、名称含“手机”的新品折扣商品。

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