duanjianao0592
2015-02-06 17:33
浏览 106
已采纳

Mysql - 按相关性排序查询,无需全文方法

I want to know how can I achieve this. I need to order a query by relevance but I cannot do that, I do not want to use the full-text + boolean mode, etc because I have a lot of records that do not appears this way (50%+ of regs) and so on, so I've create a query in a loop to search word by word and I exclude the irrelevant words by myself before doing the query, the result of the process for two words query is something like this:

SELECT UsrNames, UsrLastNames 
FROM user
WHERE 1 AND (UsrNames LIKE '%FirstWord%'
            OR UsrLastNames LIKE '%FirstWord%'
            OR UsrCI LIKE '%FirstWord%'
            OR UsrEmail LIKE '%FirstWord%'
            OR UsrRUC LIKE '%FirstWord%'
            OR UsrCod LIKE '%FirstWord%'
            OR UsrPhone LIKE '%FirstWord%'
            OR UsrNames LIKE '%SecondWord%'
            OR UsrLastNames LIKE '%SecondWord%'
            OR UsrCI LIKE '%SecondWord%'
            OR UsrEmail LIKE '%SecondWord%'
            OR UsrRUC LIKE '%SecondWord%'
            OR UsrCod LIKE '%SecondWord%'
            OR UsrPhone LIKE '%SecondWord%') ORDER BY  UsrNames ASC 
 LIMIT 20
 OFFSET 0

It is right now in development, so the don't you care about the "WHERE 1" at the beginning.

Hope this great comunity could help.

图片转代码服务由CSDN问答提供 功能建议

我想知道如何实现这一目标。 我需要按相关性订购查询,但我不能这样做 ,我不想使用全文+布尔模式等因为我有很多记录没有这种方式出现(50%以上的regs)等等,所以我在循环中创建了一个查询 在逐行搜索之前我自己排除了不相关的单词,两个单词查询过程的结果是这样的:

  SELECT UsrNames,UsrLastNames  
 
 
用户
WHERE 1 AND(UsrNames LIKE'%FirstWord%'
或者UsrLastNames LIKE'%FirstWord%'
或者UsrCI LIKE'%FirstWord%'
或者UsrEmail LIKE'%FirstWord%'
或者UsrRUC LIKE  '%FirstWord%'
或者UsrCod LIKE'%FirstWord%'
 OR UsrPhone LIKE'%FirstWord%'
 OR UsrNames LIKE'%SecondWord%'
 OR UsrLastNames LIKE'%SecondWord%'
或者UsrCI LIKE  '%SecondWord%'
 OR  UsrEmail LIKE'%SecondWord%'
 OR UsrRUC LIKE'%SecondWord%'
 OR UsrCod LIKE'%SecondWord%'
或UsrPhone LIKE'%SecondWord%')ORDER BY UsrNames ASC 
 LIMIT 20 
 OFFSET 0  
   
 
 

现在正处于开发阶段,所以你不关心开头的“WHERE 1”。 < 希望这个伟大的社区可以提供帮助。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dscojuxf69080 2015-02-06 17:49
    已采纳

    This will order by the number of matches:

    ORDER BY IF(UsrNames LIKE '%FirstWord%', 1, 0)
            + IF(UsrLastNames LIKE '%FirstWord%', 1, 0)
            + IF(UsrCI LIKE '%FirstWord%', 1, 0)
            + IF(UsrEmail LIKE '%FirstWord%', 1, 0)
            + IF(UsrRUC LIKE '%FirstWord%', 1, 0)
            + IF(UsrCod LIKE '%FirstWord%', 1, 0)
            + IF(UsrPhone LIKE '%FirstWord%', 1, 0)
            + IF(UsrNames LIKE '%SecondWord%', 1, 0)
            + IF(UsrLastNames LIKE '%SecondWord%', 1, 0)
            + IF(UsrCI LIKE '%SecondWord%', 1, 0)
            + IF(UsrEmail LIKE '%SecondWord%', 1, 0)
            + IF(UsrRUC LIKE '%SecondWord%', 1, 0)
            + IF(UsrCod LIKE '%SecondWord%', 1, 0)
            + IF(UsrPhone LIKE '%SecondWord%', 1, 0) DESC,
        UsrNames ASC
    

    FIDDLE

    The IF() expressions convert each of the comparisons to a number and totals them. If you wanted, you could even make some fields more important in the ranking by giving them a higher value than 1.

    点赞 评论

相关推荐 更多相似问题