如何优化多条件匹配折扣规则的SQL实现方案？

当满足所有条件时，将应用Great Indian Sale Discount。context可包含多个context_subCategory值：

• 负分组表示强制条件（需全部满足）；正分组编号表示可选条件（需至少满足其中一个）。

Great Indian Sale Discount 应用条件 #

强制条件 #

• context为Party，且子类别partyLocation ≠ Australia
• context为Order，且子类别orderType = cash

可选条件（至少满足其一） #

• context为Customer，且子类别customerName = Shubh
• context为Customer，且子类别customerId = 12
• context为Customer，且子类别customerLocation = Canberra

当前需从多个Discount Name中筛选符合条件的折扣，寻求更简洁实现方案。subcategory及其值均为传入参数（如partyLocation、Australia）。

现有实现使用大量CASE语句，context固定为Party、Order或Customer，但不同Discount Name对应的子类别可能不同。以下是可选条件的核心代码，同时通过mandatoryCount统计强制条件满足情况，当optionalCount和mandatoryCount均大于0时，选中对应Discount Name：

SET @sql  = 'SET @optionalCount = (select count(*) from #discountData where ' + (            
 SELECT CASE WHEN @context = 'PARTY'            
      THEN 'CONTEXT = ''' + @context + ''' and context_value= ' + (case statment for selecting subcategory )          
      WHEN @context = 'CUSTOMER'            
      THEN 'CONTEXT = ''' + @context + ''' and context_value= ' + (case statment for selecting subcategory)          
      WHEN @context = 'ORDER'            
      THEN 'CONTEXT = ''' + @context + ''' and context_value= ' + (case statment for selecting subcategory)             
      END            
) + ')'        

优化实现方案 #

方案1：表驱动式规则配置 #

预先构建折扣规则表，将每个折扣的强制/可选条件存储到表中，彻底避免硬编码CASE语句，后续新增或修改规则无需改动代码。

示例规则表结构：

实现代码：

-- 假设传入参数存储在#params表：Context, SubCategory, Value
DECLARE @mandatoryRequired INT, @mandatoryMet INT, @optionalMet INT

-- 获取当前折扣需要满足的强制条件总数
SELECT @mandatoryRequired = COUNT(*)
FROM DiscountRules
WHERE DiscountName = 'Great Indian Sale Discount' AND ConditionType = 'MANDATORY'

-- 统计已满足的强制条件数
SELECT @mandatoryMet = COUNT(*)
FROM DiscountRules dr
JOIN #params p ON dr.Context = p.Context AND dr.SubCategory = p.SubCategory
WHERE dr.DiscountName = 'Great Indian Sale Discount'
  AND dr.ConditionType = 'MANDATORY'
  AND CASE dr.Operator
        WHEN '=' THEN p.Value = dr.TargetValue
        WHEN '!=' THEN p.Value != dr.TargetValue
        -- 可扩展支持>, <, LIKE等运算符
      END

-- 统计已满足的可选条件数
SELECT @optionalMet = COUNT(*)
FROM DiscountRules dr
JOIN #params p ON dr.Context = p.Context AND dr.SubCategory = p.SubCategory
WHERE dr.DiscountName = 'Great Indian Sale Discount'
  AND dr.ConditionType = 'OPTIONAL'
  AND CASE dr.Operator
        WHEN '=' THEN p.Value = dr.TargetValue
        WHEN '!=' THEN p.Value != dr.TargetValue
      END

-- 判断是否符合折扣条件
IF @mandatoryMet = @mandatoryRequired AND @optionalMet >= 1
BEGIN
    -- 执行应用折扣的逻辑
END

方案2：简化动态SQL拼接 #

若不想引入规则表，可通过集合方式定义条件，用STRING_AGG拼接SQL语句，替代多层CASE嵌套，代码可读性和扩展性更强：

DECLARE @whereClause NVARCHAR(MAX), @optionalClause NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 构建强制条件语句
SELECT @whereClause = STRING_AGG(
    CONCAT('(CONTEXT = ''', Context, ''' AND ', SubCategory, ' ', Operator, ' ''', TargetValue, ''')'),
    ' AND '
)
FROM (
    SELECT 'PARTY' AS Context, 'partyLocation' AS SubCategory, '!=' AS Operator, 'Australia' AS TargetValue
    UNION ALL
    SELECT 'ORDER', 'orderType', '=', 'cash'
) AS MandatoryConditions

-- 构建可选条件语句
SELECT @optionalClause = STRING_AGG(
    CONCAT('(CONTEXT = ''', Context, ''' AND ', SubCategory, ' ', Operator, ' ''', TargetValue, ''')'),
    ' OR '
)
FROM (
    SELECT 'CUSTOMER', 'customerName', '=', 'Shubh'
    UNION ALL
    SELECT 'CUSTOMER', 'customerId', '=', '12'
    UNION ALL
    SELECT 'CUSTOMER', 'customerLocation', '=', 'Canberra'
) AS OptionalConditions

-- 组合完整查询语句
SET @sql = CONCAT(
    'SELECT DiscountName FROM #discountData ',
    'WHERE ', @whereClause,
    ' AND (', @optionalClause, ')'
)

EXEC sp_executesql @sql

方案3：JSON参数直接匹配 #

如果传入参数采用JSON格式，可直接利用JSON函数进行条件匹配，无需动态SQL，代码更简洁：

-- 示例传入参数JSON
DECLARE @params NVARCHAR(MAX) = '{"PARTY": {"partyLocation": "India"}, "ORDER": {"orderType": "cash"}, "CUSTOMER": {"customerName": "Shubh"}}'

SELECT DiscountName
FROM #discountData
WHERE 
    -- 强制条件1：Party的partyLocation不等于Australia
    JSON_VALUE(@params, '$.PARTY.partyLocation') != 'Australia'
    -- 强制条件2：Order的orderType等于cash
    AND JSON_VALUE(@params, '$.ORDER.orderType') = 'cash'
    -- 可选条件：Customer满足任一
    AND (
        JSON_VALUE(@params, '$.CUSTOMER.customerName') = 'Shubh'
        OR JSON_VALUE(@params, '$.CUSTOMER.customerId') = '12'
        OR JSON_VALUE(@params, '$.CUSTOMER.customerLocation') = 'Canberra'
    )

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