SQL查询如何按关键词匹配度从高到低对返回结果排序

你可以通过统计命中的关键词数量实现匹配度排序，你当前用的是MySQL语法，可直接按如下修改：

SELECT 
    a.itemId, 
    a.tags,
    -- 统计命中关键词数量，值越高匹配度越高
    (IF(a.tags LIKE '%master%', 1, 0) 
    + IF(a.tags LIKE '%guide%', 1, 0)
    + IF(a.tags LIKE '%grade%', 1, 0)
    + IF(a.tags LIKE '%10%', 1, 0)) AS match_score
FROM items a
WHERE 
    a.quantity <> 0 
    AND a.deleted <> 1 
    AND a.tags RLIKE REPLACE("master guide grade 10", ' ', '|')
ORDER BY 
    -- 优先完全匹配的记录排在最前，符合你示例的需求
    a.tags = "master guide grade 10" DESC,
    -- 再按命中关键词数量降序排序
    match_score DESC

方案说明 #

• 核心逻辑是给每个命中的关键词加1分，分数越高匹配度越高，排序越靠前
• 如果关键词是动态生成的，你只需要根据传入的关键词动态拼接对应的IF(xxx LIKE '%yyy%',1,0)统计项即可，不需要修改其他逻辑

如果你需要避免部分单词匹配（比如tags里的master10不会被误判为命中master），可以把WHERE条件里的正则修改为带单词边界的写法：

AND a.tags RLIKE CONCAT('[[:<:]]', REPLACE("master guide grade 10", ' ', '[[:>:]]|[[:<:]]'), '[[:>:]]')

统计match_score的时候也可以对应替换成正则匹配的判断，避免部分匹配计分。

MySQL 8.0+ 简化写法 #

如果你的数据库版本是MySQL 8.0及以上，还可以用内置的正则计数函数，不需要逐词写判断：

SELECT 
    a.itemId, 
    a.tags,
    -- 直接统计正则匹配到的单词数量
    REGEXP_COUNT(a.tags, REPLACE("master guide grade 10", ' ', '|')) AS match_score
FROM items a
WHERE 
    a.quantity <> 0 
    AND a.deleted <> 1 
    AND a.tags RLIKE REPLACE("master guide grade 10", ' ', '|')
ORDER BY 
    a.tags = "master guide grade 10" DESC,
    match_score DESC

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