SQL Server表关联返回多行结果，需优化为仅返回匹配度最高的单行

问题背景 #

• table1 存储订单数据，feature字段用分号分隔多个特征值
• table2 是特征映射表，通过feature_1、feature_2、feature_3定义不同特征组合，每个组合对应唯一feature_id

需求：按3个特征全匹配 → 2个特征匹配 → 1个特征匹配的优先级，为每个订单返回唯一的feature_id，但当前SQL关联后会返回多行结果，需要调整。

当前使用的SQL：

SELECT
      order_id,
      feature,
      fm.feature_id
FROM table1
INNER JOIN table2 AS fm on 
(
fm.feature_1 is not null AND fm.feature_2 is not null AND fm.feature_3 is not null AND
feature like CONCAT('%', fm.feature_1, '%') AND
feature like CONCAT('%', fm.feature_2, '%') AND
feature like CONCAT('%', fm.feature_3, '%')
)
OR
(
fm.feature_1 is not null AND fm.feature_2 is not null AND 
fm.feature_3 is null AND feature like CONCAT('%', fm.feature_1, '%') AND
feature like CONCAT('%', fm.feature_2, '%')
)
OR
(
fm.feature_1 is not null AND fm.feature_2 is null AND 
fm.feature_3 is null AND
feature like CONCAT('%', fm.feature_1, '%')
)

优化方案 #

核心思路是给每个匹配结果标记优先级得分，再通过窗口函数筛选每个订单的最高优先级匹配项：

SELECT order_id, feature, feature_id
FROM (
    SELECT
        t1.order_id,
        t1.feature,
        fm.feature_id,
        -- 计算匹配优先级得分：3个全匹配得3分，2个得2分，1个得1分
        CASE
            WHEN fm.feature_1 IS NOT NULL AND fm.feature_2 IS NOT NULL AND fm.feature_3 IS NOT NULL 
                 AND t1.feature LIKE CONCAT('%', fm.feature_1, '%') 
                 AND t1.feature LIKE CONCAT('%', fm.feature_2, '%') 
                 AND t1.feature LIKE CONCAT('%', fm.feature_3, '%') THEN 3
            WHEN fm.feature_1 IS NOT NULL AND fm.feature_2 IS NOT NULL AND fm.feature_3 IS NULL 
                 AND t1.feature LIKE CONCAT('%', fm.feature_1, '%') 
                 AND t1.feature LIKE CONCAT('%', fm.feature_2, '%') THEN 2
            WHEN fm.feature_1 IS NOT NULL AND fm.feature_2 IS NULL AND fm.feature_3 IS NULL 
                 AND t1.feature LIKE CONCAT('%', fm.feature_1, '%') THEN 1
            ELSE 0
        END AS match_score,
        -- 按订单分组，按得分降序排序，取每组第一行
        ROW_NUMBER() OVER (PARTITION BY t1.order_id ORDER BY match_score DESC) AS rn
    FROM table1 t1
    INNER JOIN table2 fm ON 
        (
            fm.feature_1 IS NOT NULL AND fm.feature_2 IS NOT NULL AND fm.feature_3 IS NOT NULL 
            AND t1.feature LIKE CONCAT('%', fm.feature_1, '%') 
            AND t1.feature LIKE CONCAT('%', fm.feature_2, '%') 
            AND t1.feature LIKE CONCAT('%', fm.feature_3, '%')
        )
        OR
        (
            fm.feature_1 IS NOT NULL AND fm.feature_2 IS NOT NULL AND fm.feature_3 IS NULL 
            AND t1.feature LIKE CONCAT('%', fm.feature_1, '%') 
            AND t1.feature LIKE CONCAT('%', fm.feature_2, '%')
        )
        OR
        (
            fm.feature_1 IS NOT NULL AND fm.feature_2 IS NULL AND fm.feature_3 IS NULL 
            AND t1.feature LIKE CONCAT('%', fm.feature_1, '%')
        )
) ranked
WHERE rn = 1;

关键说明 #

1. 优先级得分计算：通过CASE语句明确每个匹配场景的得分，确保3特征匹配优先级最高
2. 窗口函数去重：ROW_NUMBER()按order_id分组，按得分降序排序后，取rn=1的行，保证每个订单仅返回一条结果
3. 潜在优化点：当前用LIKE '%xxx%'可能会匹配到子串（比如特征"abc"会匹配到"abcd"），如果需要精确匹配分号分隔的特征，可以用字符串分割函数替换LIKE，例如MySQL环境下：

   -- 精确匹配单个特征
   FIND_IN_SET(fm.feature_1, REPLACE(t1.feature, ';', ',')) > 0
   

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