Question

SQL 搜索结果相关性排序问题

Answer 1

补充，我上面的方法将不包含数据库或SQL的也显示出来了，可将SQL语句改为：
Select
Title,Content,case
when
Title
like
'%数据库%'
and
Title
like
'%Sql%'
then
1
when
Content
like
'%数据库%'
and
Content
like
'%Sql%'
then
2
when
Title
like
'%数据库%'
or
Title
like
'%Sql%'
then
3
when
Content
like
'%数据库%'
or
Content
like
'%Sql%'
then
4
end
as
priority
from
Article
where
Title
like
'%数据库%'
or
Title
like
'%Sql%'
or
Content
like
'%数据库%'
or
Content
like
'%Sql%'
order
by
priority,tTime
desc

Answer 2

如果不按时间排序，只按
标题中同时出现"数据库"和"Sql"
>>
内容中同时出现"数据库"和"Sql"
>>
标题中出现"数据库"或"Sql"
>>内容中出现"数据库"或"Sql"
排序可以用：
Select
Title,Content
from
Article
where
Title
like
'%数据库%'
and
Title
like
'%Sql%'
union
all
Select
Title,Content
from
Article
where
not
(Title
like
'%数据库%'
and
Title
like
'%Sql%')
and
Content
like
'%数据库%'
and
Content
like
'%Sql%'
union
all
Select
Title,Content
from
Article
where
not
(Title
like
'%数据库%'
and
Title
like
'%Sql%')
and
not
(Content
like
'%数据库%'
and
Content
like
'%Sql%')
and
(Title
like
'%数据库%'
or
Title
like
'%Sql%')
union
all
Select
Title,Content
from
Article
where
Title
not
like
'%数据库%'
and
Title
not
like
'%Sql%'
and
not
(Content
like
'%数据库%'
and
Content
like
'%Sql%')
and
(Content
like
'%数据库%'
or
Content
like
'%Sql%')

Answer 3

如果先按标题中同时出现"数据库"和"Sql"
>>
内容中同时出现"数据库"和"Sql"
>>
标题中出现"数据库"或"Sql"
>>内容中出现"数据库"或"Sql"
再按时间降序只能用添加辅助列的方法实现：
Select
Title,Content,case
when
Title
like
'%数据库%'
and
Title
like
'%Sql%'
then
1
when
Content
like
'%数据库%'
and
Content
like
'%Sql%'
then
2
when
Title
like
'%数据库%'
or
Title
like
'%Sql%'
then
3
when
Content
like
'%数据库%'
or
Content
like
'%Sql%'
then
4
end
as
priority
from
Article
order
by
priority,tTime
desc