TSQL按多列最高匹配规则为事实表新增映射CCODE字段值

你原有SQL存在3个核心缺陷，导致匹配结果不符合预期：

• 排序逻辑错误：仅对映射表全局按Number字段倒序排列，既没有计算Number前缀实际匹配长度，也未将FCODE匹配规则纳入优先级判断，RANK函数的作用范围完全不符合需求
• 关联条件缺失：未加入FCODE匹配判断，映射表中FCODE为通配符、FCODE精确匹配两类规则没有做区分校验，会关联出大量不符合规则的记录
• 无结果筛选逻辑：关联后会返回单条事实表对应的多条匹配记录，没有筛选优先级最高的单条记录

先把规则转化为可排序的量化条件，避免逻辑歧义：

1. 第一排序键：Number前缀匹配长度，长度越长优先级越高（例如匹配4位前缀0123优先级高于3位前缀012）
2. 第二排序键：相同Number匹配长度下，FCODE精确匹配（映射表FCODE为固定值）优先级高于FCODE通配（映射表FCODE全为*）

用OUTER APPLY针对每条事实表记录单独匹配优先级最高的映射规则，避免全局排序的逻辑问题：

SELECT 
    f.Number,
    f.FCODE,
    m.CCODE
FROM dbo.Test_Fact f
OUTER APPLY (
    SELECT TOP 1 t.CCODE
    FROM dbo.Test_Mapping t
    -- 预计算当前映射规则的Number非星号前缀长度
    CROSS APPLY (
        SELECT LEN(LEFT(t.Number, CHARINDEX('*', t.Number) - 1)) AS NumPrefixLen
    ) np
    WHERE 
        -- 校验Number前缀匹配
        LEFT(f.Number, np.NumPrefixLen) = LEFT(t.Number, np.NumPrefixLen)
        -- 校验FCODE匹配：要么映射表FCODE全为星号（通配），要么和事实表FCODE完全相等
        AND (t.FCODE = REPLICATE('*', LEN(t.FCODE)) OR t.FCODE = f.FCODE)
    -- 按优先级从高到低排序，取第一条
    ORDER BY 
        np.NumPrefixLen DESC,
        CASE WHEN t.FCODE = REPLICATE('*', LEN(t.FCODE)) THEN 0 ELSE 1 END DESC
) m

基于你提供的示例数据，执行上述SQL返回结果完全符合预期：

匹配逻辑说明：

• 0123456789 + 12345：最长匹配4位前缀0123，对应CCODE为6
• 0123456789 + 545243：4位前缀规则的FCODE为12345不匹配，退而匹配3位前缀012的FCODE通配规则，对应CCODE为3
• 0129456789 + 545243：无4位前缀匹配规则，匹配3位前缀012的FCODE通配规则，对应CCODE为3
• 0129456789 + 12345：无4位前缀匹配规则，3位前缀下FCODE精确匹配12345的规则优先级高于通配规则，对应CCODE为4

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