我现在要对表中的数据进行搜索,主要就是基于一个关键词同时对标题和内容字段进行搜索,描述字段为备注型,现在都所得时候是这样,举例:Select Title,Content from Article where Title like '%数据库% and Title like '%Sql%' and Content like '%数据库% and Content like '%Sql%' order by tTime desc现在的排序结果是按照时间的。可是内容基本上是乱七八糟。因为我想要得结果是标题中同时出现"数据库"和"Sql" >> 内容中同时出现"数据库"和"Sql" >> 标题中出现"数据库"或"Sql" >>内容中出现"数据库"或"Sql" 这应该算是一个优先问题吧,用union似乎不能够作用于Text型字段上。用全文所索引的Contains似乎也只是同一个字段才有权重设置,不同字段之间也不行。。这种技术现在搜索引擎基本都在用。怎么来搞搞?给个思路也好。万分感谢~ 分就这么点了。包含~
Select Title,Content from Article where (Title like '%数据库% and Title like '%Sql%') or (Content like '%数据库% and Content like '%Sql%') order by tTime desc
按优先写入临时表,
然后一起Select
union
Select Title,Content from Article where (Content like '%数据库%' and Content like '%Sql%')
union
Select Title,Content from Article where (Title like '%数据库%' or Title like '%Sql%')
union
Select Title,Content from Article where (Content like '%数据库%' or Content like '%Sql%')
select Title,content,
case
when Title like '%数据库%' and Title like '%sql%' then 10
when Content like '%数据库%' and Content like '%sql%' then 5
else 0
end as t2
from Article
where (Title like '%数据库%' and Title like '%sql%' ) or (Content like '%数据库%' and Content like '%sql%' )
order by t2 desc