CROSS APPLY逻辑异常求助：ProductDeliveryRules规则匹配问题

我有一张ProductDeliveryRules表，用来定义产品能卖去哪些国家，规则是这样的：

• CategoryId为12的产品，只能卖去'IT'；
• RuleGroup=5的规则，必须同时满足DepartmentId=10和SupplierTypeId=1；
• 如果产品符合多个RuleGroup的条件，只认RulePriority最低的那个；
• 要是啥规则都不符合，默认能卖去所有国家，用'**'表示。

我写了下面这段SQL，但死活拿不到正确的RuleGroup，折腾好几天了：

SELECT i.ProductId, 
       ValidRule.RuleGroup -- 无匹配规则组时返回NULL
FROM inserted i
join Brand b on b.IDBrand = i.IDBrand
join Suppliers s on s.SupplierId = b.SupplierId

CROSS APPLY (
    SELECT TOP 1 CASE WHEN NOT EXISTS (
        SELECT 1
        FROM ProductDeliveryRules pdr2
        WHERE pdr2.RuleGroup = PDR.RuleGroup
        AND (
            (PDR2.[KeyType] = 'CategoryId' AND PDR2.[Key] != i.IDCategory)
            OR
            (PDR2.[KeyType] = 'MaxWeight' AND i.param <= PDR2.[Key])
            OR
            (PDR2.[KeyType] = 'DepartmentId' AND PDR2.[Key] != i.IDDepartment)
            OR
            (PDR2.[KeyType] = 'SupplierTypeId' AND PDR2.[Key] != f.IDSupplierType)
        )
    ) 
    THEN PDR.RuleGroup ELSE NULL END as RuleGroup
    FROM ProductDeliveryRules PDR
    ORDER BY PDR.RulePriority ASC
) AS ValidRule

问题点和修正方案 #

原代码有几个明显的坑：

1. 逻辑搞反了：判断规则组是否匹配时，原代码用了!=排除符合条件的情况，比如CategoryId规则，本该是产品IDCategory等于规则Key才算符合，结果写了不等于，直接把匹配项排除了。
2. 条件逻辑错误：规则组要求全部条件满足才算匹配，但你用OR连接条件，只要有一个条件不满足就否定整个规则组，逻辑完全颠倒。
3. 别名写错了：代码里用了f.IDSupplierType，但根本没定义表别名f，应该是s.IDSupplierType（关联的Suppliers表别名是s）。
4. CROSS APPLY不适合无匹配场景：CROSS APPLY在没有匹配结果时会直接过滤掉数据，而我们需要保留这些数据并返回默认值，应该用OUTER APPLY。

下面是修正后的代码：

SELECT 
    i.ProductId,
    COALESCE(ValidRule.RuleGroup, '**') AS TargetCountry
FROM inserted i
JOIN Brand b ON b.IDBrand = i.IDBrand
JOIN Suppliers s ON s.SupplierId = b.SupplierId
OUTER APPLY (
    SELECT TOP 1 PDR.RuleGroup
    FROM ProductDeliveryRules PDR
    GROUP BY PDR.RuleGroup, PDR.RulePriority
    HAVING 
        -- 规则组内所有条件都满足才通过
        SUM(CASE 
            WHEN PDR.KeyType = 'CategoryId' AND PDR.[Key] = i.IDCategory THEN 0
            WHEN PDR.KeyType = 'MaxWeight' AND i.param <= PDR.[Key] THEN 0
            WHEN PDR.KeyType = 'DepartmentId' AND PDR.[Key] = i.IDDepartment THEN 0
            WHEN PDR.KeyType = 'SupplierTypeId' AND PDR.[Key] = s.IDSupplierType THEN 0
            -- 不满足的条件计1，总和为0表示全满足
            ELSE 1
        END) = 0
    -- 取优先级最低的规则组
    ORDER BY PDR.RulePriority ASC
) AS ValidRule

为啥这么改？ #

• 用OUTER APPLY替代CROSS APPLY，确保没有匹配规则组时也能保留产品数据，再用COALESCE把NULL换成默认的'**'。
• 按RuleGroup分组，用HAVING统计不满足的条件数量，总和为0就说明这个规则组的所有条件都符合产品。
• 排序用RulePriority ASC，保证拿到的是优先级最低的那个规则组。

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