如何在MySQL ORDER BY的CASE子句中实现多词多次出现排序

要实现所有关键词在文章中多次出现的排序优先级（介于规则1和规则3之间），核心是统计每个关键词在文章内容字段（如B.description）中的出现次数，判断所有关键词的出现次数都≥2，再在ORDER BY子句中插入对应的排序逻辑。

具体实现步骤 #

1. 统计关键词出现次数：利用MySQL字符串函数计算关键词出现次数，公式为：

   -- 统计"EXAMPLES"的出现次数
   (LENGTH(B.description) - LENGTH(REPLACE(B.description, 'EXAMPLES', ''))) / LENGTH('EXAMPLES')
   -- 统计"OF"的出现次数
   (LENGTH(B.description) - LENGTH(REPLACE(B.description, 'OF', ''))) / LENGTH('OF')
   

   注：若需区分大小写，可在LIKE或REPLACE前添加BINARY修饰；如果关键词是动态传入的，需用预处理语句避免SQL注入。

2. 插入排序逻辑：在原ORDER BY的规则1之后、规则3之前，添加判断所有关键词出现次数≥2的CASE语句，设置优先级为2（保证排序顺序符合规则要求）。

修改后的完整ORDER BY子句 #

ORDER BY 
  -- 规则1：所有关键词在标题中（保留原逻辑）
  (CASE WHEN B.page_name LIKE '%EXAMPLES%' AND B.page_name LIKE '%OF%' AND B.lifetime_views THEN 1 END) DESC,
  -- 规则2：所有关键词在文章中多次出现（新增逻辑）
  (CASE WHEN 
    ((LENGTH(B.description) - LENGTH(REPLACE(B.description, 'EXAMPLES', ''))) / LENGTH('EXAMPLES')) >= 2
    AND 
    ((LENGTH(B.description) - LENGTH(REPLACE(B.description, 'OF', ''))) / LENGTH('OF')) >= 2
    AND B.lifetime_views 
  THEN 2 END) DESC,
  -- 规则3：所有关键词在文章中（保留原逻辑，调整优先级为3）
  (CASE WHEN B.description LIKE '%EXAMPLES%' AND B.description LIKE '%OF%' AND B.lifetime_views THEN 3 END) DESC,
  -- 规则4：任一关键词在标题中（保留原逻辑，调整优先级为4）
  (CASE WHEN (B.page_name LIKE '%EXAMPLES%' OR B.description LIKE '%EXAMPLES%') AND (B.page_name LIKE '%OF%' OR B.description LIKE '%OF%') AND B.lifetime_views THEN 4 END) DESC,
  -- 规则5：任一关键词在文章中（补充原缺失的规则5逻辑）
  (CASE WHEN (B.description LIKE '%EXAMPLES%' OR B.tags LIKE '%EXAMPLES%') AND (B.description LIKE '%OF%' OR B.tags LIKE '%OF%') AND B.lifetime_views THEN 5 END) DESC

补充说明 #

如果"文章内容"包含多个字段（如description+tags），可合并字段后再统计次数：

LENGTH(CONCAT(B.description, B.tags)) - LENGTH(REPLACE(CONCAT(B.description, B.tags), 'EXAMPLES', ''))

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