SQL Server如何高效实现多字段优先级匹配查询

核心思路 #

抛弃逐层枚举字段组合拼接WHERE条件的实现方式，改用匹配度打分排序的逻辑，一次查询即可得到结果，字段扩展时不需要追加大量冗余查询语句。
逻辑完全对齐需求规则：

• 每命中1个维度的等值匹配，该行计1分，总分即为该行匹配的字段数量
• 过滤掉总分为0（无任何字段匹配）的行
• 按总分从高到低排序，取第一条的金额值，无符合条件的行则返回0
• 匹配字段数越多的行得分越高，天然满足「从4字段全匹配到单字段匹配逐层校验」的优先级要求

实现代码（以MySQL语法为例） #

-- 定义输入参数，实际业务中替换为接口/程序传入的参数即可
SET @input_state = 'NJ';
SET @input_type = 'Drawing';
SET @input_client = 'US';
SET @input_website = 'google.com';

SELECT COALESCE(
  (
    SELECT Amount
    FROM Incentive
    -- 排除所有字段都不匹配的行
    WHERE 
      (State = @input_state)
      + (Type = @input_type)
      + (Client = @input_client)
      + (Website = @input_website) > 0
    -- 按匹配字段数从高到低排序，同匹配度下可追加主键/优先级字段排序
    ORDER BY 
      (State = @input_state)
      + (Type = @input_type)
      + (Client = @input_client)
      + (Website = @input_website) DESC,
      id ASC -- 追加此项即可按配置顺序取同匹配度的首条规则，得到预期的10的返回结果
    LIMIT 1
  ),
0) AS matched_incentive;

针对给出的示例输入，上述语句执行时各数据行的得分计算如下：

• 行1（NJ/0/0/google.com/10）：State匹配、Website匹配，总分2
• 行2（0/0/CA/google.com/3）：仅Website匹配，总分1
• 行3（NJ/0/US/google.com/10）：State匹配、Client匹配、Website匹配，总分3
• 行4（NJ/Typing/US/google.com/15）：State匹配、Client匹配、Website匹配，总分3

扩展与优化说明 #

• 新增维度列时，仅需要在WHERE和ORDER BY的得分计算段新增(新字段名 = @对应输入参数)的判断项即可，不需要枚举所有字段组合，代码量不会随字段数增长呈组合级膨胀，维护成本极低。
• 如需调整不同字段的匹配权重，直接修改对应判断项的计分系数即可，比如需要让State字段的匹配优先级高于其他字段，可将(State = @input_state)修改为(State = @input_state) * 10，其余字段保持计1分，即可实现State匹配的行永远排在其他字段匹配的行前面。
• 性能层面，该查询仅需要扫描一次表，远优于多层UNION拼接、多次查询判断的实现方式；数据量较大时，可结合查询频率给高频维度组合建联合索引，进一步降低执行耗时。

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