php - 按大多数匹配项选择和排序

Question

假设我分解了搜索中传递的字符串。示例: “if there was a dog” “if there were a dog”(愚蠢的美国人)。

我们根据“”展开所以结果...

if
there
were
a
dog

现在我想运行一个 SQL select * from table_name query where column_name like
“%something%”或 column_name，如“%somethingelse%”...

我正在尝试确定如何搜索表格并按包含最多匹配项的行进行排序。 (即，如果 45 行包含 4 上述拆分项，而 21 行仅包含 2，则 2 行strong>45 应该显示在结果的顶部)。

这将是一个原始的“搜索相关性”逻辑。 SQL 中是否有针对这种检索的特定术语？

建议？

Answer 1

只需将比较放在order by 子句中，使用case 语句将它们转换为0/1，然后将它们相加:

select *
from table_name query
where column_name like '%something%' or column_name like '%somethingelse%'
order by ((case when column_name like '%something%' then 1 else 0 end) +
          (case when column_name like '%somethingelse%' then 1 else 0 end)
          . . .
         ) desc

我倾向于将查询写成:

select (Match1+Match2+. . .) as NumMatches, <rest of columns>
from (select t.*,
             (case when column_name like '%something%' then 1 else 0 end) as Match1,
             . . .
      from tablename
     ) t
order by NumMatches desc