MySQL按“最佳匹配"排序

2021-11-20 00:00:00 sql mysql sql-order-by

我有一个包含单词的表格和一个输入字段，可以使用实时搜索来搜索该表格.目前，我使用以下查询来搜索表:

I have a table that contains words and an input field to search that table using a live search. Currently, I use the following query to search the table: 

SELECT word FROM words WHERE word LIKE '%searchstring%' ORDER BY word ASC

有没有办法对结果进行排序，以便在单词开头找到字符串的那些放在最前面，而在单词中出现在后面的那些放在最后?

Is there a way to order the results so that the ones where the string is found at the beginning of the word come first and those where the string appears later in the word come last?

示例:搜索hab"当前返回

  1. 一个字母表
  2. h 升技
  3. r ehab
  1. a lphabet
  2. h abit
  3. r ehab

但我喜欢这样:

  1. hab 它(首先是因为 'hab' 是开始)
  2. alp hab et(第二个是因为'hab'在单词中间)
  3. re hab(最后是因为hab"在词尾)
  1. hab it (first because 'hab' is the beginning)
  2. alp hab et (second because 'hab' is in the middle of the word)
  3. re hab (last because 'hab' is at the end of the word)

或者至少是这样:

  1. hab 它(首先是因为 'hab' 是开始)
  2. re hab(第二个是因为 'hab' 从第三个字母开始)
  3. alp hab et(最后，因为 'hab' 最晚开始，在第四个字母处)
  1. hab it (first because 'hab' is the beginning)
  2. re hab (second because 'hab' starts at the third letter)
  3. alp hab et (last because 'hab' starts latest, at the fourth letter)

如果有人能帮我解决这个问题就好了！

Would be great if anyone could help me out with this!

推荐答案

按照第一种方式(开始单词，在单词中间，结束单词)，尝试这样的事情:

To do it the first way (starts word, in the middle of the word, ends word), try something like this:

SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY
  CASE
    WHEN word LIKE 'searchstring%' THEN 1
    WHEN word LIKE '%searchstring' THEN 3
    ELSE 2
  END

要使用第二种方式(匹配字符串的位置)，请使用 LOCATE 函数:

To do it the second way (position of the matched string), use the LOCATE function:

SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY LOCATE('searchstring', word)

您可能还需要一个决胜局，例如，多个单词以 hab 开头.为此，我建议:

You may also want a tie-breaker in case, for example, more than one word starts with hab. To do that, I'd suggest:

SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY <whatever>, word

如果有多个以hab开头的词，以hab开头的词会组合在一起，并按字母顺序排序.

In the case of multiple words starting with hab, the words starting with hab will be grouped together and sorted alphabetically.

相关文章