如何基于多规则关联SQL表？处理通配符与多值匹配场景

问题背景 #

现有两张业务表，结构及数据如下：

销售维度表 #

SALEID  Shop    Channel   Value     Products     Opted In?  New/Repeat  
1        HOME    EMAIL    MEDIUM    CAR          OPTED IN   NEW          
2        GARDEN  PHONE    MEDIUM    INSURANCE    OPTED IN   REPEAT       

规则表 #

Shop      Channel   Value   Products        Opted in?     New/Repeat    RuleID         IsActive
All       Email     Medium  Car,Insurance   Opted In        All           888               1
Garden    Phone     Medium  Insurance       Opted In       Repeat         834               0

需求 #

关联两张表，获取IsActive=1的RuleID，需满足规则表的所有匹配条件。

核心难点 #

• 规则表部分字段使用All作为通配符，需匹配销售表对应字段的任意值
• 规则表Products字段为逗号分隔的多值集合，需判断销售表的单值Products是否属于该集合

解决方案（通用SQL实现） #

以下SQL通过条件判断结合字符串拆分函数解决上述问题：

SELECT 
    s.SALEID,
    r.RuleID
FROM 
    销售维度表 s
INNER JOIN 
    规则表 r ON 
        -- Shop字段：All匹配任意值，否则精确匹配
        (r.Shop = 'All' OR s.Shop = r.Shop)
        -- Channel字段：统一大小写后精确匹配
        AND LOWER(r.Channel) = LOWER(s.Channel)
        -- Value字段：统一大小写后精确匹配
        AND LOWER(r.Value) = LOWER(s.Value)
        -- Products字段：拆分规则表的多值，判断销售表的产品是否在列表中
        AND EXISTS (
            SELECT 1
            FROM STRING_SPLIT(r.Products, ',') rp
            WHERE LOWER(TRIM(rp.value)) = LOWER(s.Products)
        )
        -- Opted In?字段：统一大小写后精确匹配
        AND LOWER(r."Opted in?") = LOWER(s."Opted In?")
        -- New/Repeat字段：All匹配任意值，否则精确匹配
        AND (r."New/Repeat" = 'All' OR LOWER(r."New/Repeat") = LOWER(s."New/Repeat"))
WHERE 
    r.IsActive = 1;

关键逻辑说明 #

1. 通配符All处理：对Shop和New/Repeat字段，用OR逻辑实现“匹配任意值”的需求，当规则值为All时自动满足条件，否则执行精确匹配。
2. 多值Products匹配：利用STRING_SPLIT函数将规则表的逗号分隔产品列表拆分为单行数据，通过EXISTS子查询验证销售表的产品是否在拆分后的列表中；TRIM和LOWER用于处理可能的空格和大小写差异。
3. 大小写兼容：所有字符串匹配字段统一转换为小写（或大写），避免因大小写不一致导致的匹配失败。

执行结果 #

上述SQL运行后将返回符合条件的记录：

SALEID  RuleID
1       888
2       888

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