SQL 包含表和关键字在字符串中的位置
有一个关于全文搜索的问题。
我有一个有效的查询，但我想改进它。例如，假设我正在寻找“最佳解决方案”。在我的结果中（使用 FTS、CONTAINSTABLE、NEAR、ISABOUT、*），我有列 rank、id、string：
rank| id | string ----+----+------- 430 | 33 | my own best solution 430 | 32 | the way for best solution 430 | 30 | the best solution sample 430 | 31 | best solution 430 | 34 | best solution creation how 300 | 40 | usefull best software solution 300 | 41 | best software solution 200 | 50 | wolrds bests solutions 200 | 51 | bests solutions of the world
所以这个查询对我来说 100% 正确，并且所有 RANK code>s 是正确的，但我想让这个查询更相关。
如果关键字在字符串中的位置更靠左，则它应该出现在结果的前面。例如：
rank| id | string ----+----+------- 430 | 31 | best solution 430 | 34 | best solution creation how 430 | 30 | the best solution sample 430 | 33 | my own best solution 430 | 32 | the way for best solution 300 | 41 | best software solution 300 | 40 | usefull best software solution 200 | 51 | bests solutions of the world 200 | 50 | wolrds bests solutions
这可能吗？如果是这样，我怎样才能得到这个结果？
Have a question about full text search.
I have a query that works, but I want to improve it. For example, suppose I'm searching for "best solution". In my result (using FTS,CONTAINSTABLE,NEAR,ISABOUT,*) I have the columns rank, id, string:
rank| id | string ----+----+------- 430 | 33 | my own best solution 430 | 32 | the way for best solution 430 | 30 | the best solution sample 430 | 31 | best solution 430 | 34 | best solution creation how 300 | 40 | usefull best software solution 300 | 41 | best software solution 200 | 50 | wolrds bests solutions 200 | 51 | bests solutions of the world
So this query is 100% right for me, and all RANKs are correct, but I want to make this query more relevant.
If a keyword's position in a string is more to the left, it should appear earlier in the result. For example:
rank| id | string ----+----+------- 430 | 31 | best solution 430 | 34 | best solution creation how 430 | 30 | the best solution sample 430 | 33 | my own best solution 430 | 32 | the way for best solution 300 | 41 | best software solution 300 | 40 | usefull best software solution 200 | 51 | bests solutions of the world 200 | 50 | wolrds bests solutions
Is this possible? If so, how can I get this result?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要一个
indexOf函数。在 sql 手册中搜索类似的函数。然后添加一个排序条件，如下所示：
ORDER BYrank, CASE indexOf(string, 'best') WHEN -1 THEN 100000 ELSE indexOf(string, 'best') END。如果您找不到执行某种
indexOf功能的函数，请使用数据库管理器的CREATE FUNCTION功能自行编写。You need an
indexOffunction. Search your sql manual for a similar function.Then add a sorting criteria looking like
ORDER BY rank, CASE indexOf(string, 'best') WHEN -1 THEN 100000 ELSE indexOf(string, 'best') END.If you can't find a function doing a kind of
indexOf, write it yourself using theCREATE FUNCTIONfeature of your database manager.您想要的第三方解决方案是 Sphinx（还有其他解决方案）。阅读更多相关信息：
http://www.ioncannon.net /programming/685/full-text-search-with-sphinx/
The third party solution you want is Sphinx (there are still others). Read more about here:
http://www.ioncannon.net/programming/685/full-text-search-with-sphinx/