如何创建一个SQL查询,该查询使用LIKE运算符匹配多个字符串,然后根据得到的匹配数来匹配ORDERS结果?
另外,有没有一种方法来存储匹配的数量,以便以后可以在PHP中使用该数量进行其他计算?
我正在处理另一个堆栈溢出答案中的代码

SELECT searchtopics_topicid, searchtopics_content FROM searchtopics
    WHERE searchtopics_content
         LIKE '%string1%' OR searchtopics_content
         LIKE '%string2%' OR searchtopics_content
         LIKE '%string3%'
    ORDER BY
         case when searchtopics_content LIKE '%string1%' then 1 else 0 end +
         case when searchtopics_content LIKE '%string2%' then 1 else 0 end +
         case when searchtopics_content LIKE '%string3%' then 1 else 0 end
    DESC

最佳答案

上面的代码看起来应该工作得很好,可以存储您只需将ORDER BYcase逻辑添加到select列表中的数字。

SELECT searchtopics_topicid
     , searchtopics_content
     , case when searchtopics_content LIKE '%string1%' then 1 else 0 end +
       case when searchtopics_content LIKE '%string2%' then 1 else 0 end +
       case when searchtopics_content LIKE '%string3%' then 1 else 0 end AS hit_ct
FROM searchtopics
WHERE searchtopics_content LIKE '%string1%'
   OR searchtopics_content LIKE '%string2%'
   OR searchtopics_content LIKE '%string3%'
ORDER BY
     case when searchtopics_content LIKE '%string1%' then 1 else 0 end +
     case when searchtopics_content LIKE '%string2%' then 1 else 0 end +
     case when searchtopics_content LIKE '%string3%' then 1 else 0 end
DESC

在MySQL中,您可以简化这一切:
    SELECT searchtopics_topicid
         , searchtopics_content
         , searchtopics_content LIKE '%string1%'
           + searchtopics_content LIKE '%string2%'
           + searchtopics_content LIKE '%string3%' AS hit_ct
    FROM searchtopics
    WHERE searchtopics_content LIKE '%string1%'
        + searchtopics_content LIKE '%string2%'
        + searchtopics_content LIKE '%string3%' > 0
    ORDER BY searchtopics_content LIKE '%string1%'
           + searchtopics_content LIKE '%string2%'
           + searchtopics_content LIKE '%string3%' DESC

10-05 20:28
查看更多