如何创建一个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 BY
case逻辑添加到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