假设我分解搜索中传递的字符串。示例:"if there a dog""if thee a dogs"(愚蠢的美国人)。
我们根据"爆炸,所以结果。。。
if
there
were
a
dog
现在我想运行SQL select * from table_name query where column_name like
'%something%' or column_name like '%somethingelse%'...
我正在尝试确定如何搜索表并按包含最多匹配项的行排序。(即,如果第45行包含上述拆分项目中的4,而第21列仅包含2时,则第45 这将是一个原始的"搜索相关性"逻辑。SQL中是否有专门的术语来描述这种检索? 建议?
只需将比较放在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
以下是@Gordon出色答案的变体:
SELECT FieldToSearch,
CASE WHEN ' ' + FieldToSearch + ' ' like '% if %' then 1 else 0 end
+ CASE WHEN ' ' + FieldToSearch + ' ' like '% there %' then 1 else 0 end
+ CASE WHEN ' ' + FieldToSearch + ' ' like '% was %' then 1 else 0 end
+ CASE WHEN ' ' + FieldToSearch + ' ' like '% a %' then 1 else 0 end
+ CASE WHEN ' ' + FieldToSearch + ' ' like '% dog %' then 1 else 0 end matches
FROM YourTable
ORDER BY matches DESC
这是Fiddle。
祝你好运!