SQL全文搜索结果优先级

Question

我习惯使用 Lucene 进行全文搜索，它运行良好，但我的主要数据库是 SQL。我不喜欢有两种数据库机制的想法，并决定使用 SQL 全文搜索。一切都很好，但仍有一些事情我还没有弄清楚。
假设我有下表:



所有字段都被索引以进行全文搜索。

现在我想用文本“Isaac”在这个表上进行全文搜索。我希望第 5 行位于顶部，其余结果位于其下方。
我可以如何设置一个字段的优先级，以便在该字段上找到的任何内容都将保留在结果的顶部？基本上我想按领域优先考虑它。在我的真实表中，我有 6 个字段。

Answer 1

据我所知 MSSQL 不支持字符串相似度比较..你必须编写自己的一组函数

-- get percentage diff
CREATE FUNCTION [dbo].[GetPercentageOfTwoStringMatching]
(
    @string1 NVARCHAR(100)
    ,@string2 NVARCHAR(100)
)
RETURNS INT
AS
BEGIN

    DECLARE @levenShteinNumber INT

    DECLARE @string1Length INT = LEN(@string1)
    , @string2Length INT = LEN(@string2)
    DECLARE @maxLengthNumber INT = CASE WHEN @string1Length > @string2Length THEN @string1Length ELSE @string2Length END

    SELECT @levenShteinNumber = [dbo].[LEVENSHTEIN] (   @string1  ,@string2)

    DECLARE @percentageOfBadCharacters INT = @levenShteinNumber * 100 / @maxLengthNumber

    DECLARE @percentageOfGoodCharacters INT = 100 - @percentageOfBadCharacters

    -- Return the result of the function
    RETURN @percentageOfGoodCharacters

END

-- get diff of strings 
CREATE FUNCTION [dbo].[LEVENSHTEIN](@left  VARCHAR(100),
                                    @right VARCHAR(100))
returns INT
AS
  BEGIN
      DECLARE @difference    INT,
              @lenRight      INT,
              @lenLeft       INT,
              @leftIndex     INT,
              @rightIndex    INT,
              @left_char     CHAR(1),
              @right_char    CHAR(1),
              @compareLength INT

      SET @lenLeft = LEN(@left)
      SET @lenRight = LEN(@right)
      SET @difference = 0

      IF @lenLeft = 0
        BEGIN
            SET @difference = @lenRight

            GOTO done
        END

      IF @lenRight = 0
        BEGIN
            SET @difference = @lenLeft

            GOTO done
        END

      GOTO comparison

      COMPARISON:

      IF ( @lenLeft >= @lenRight )
        SET @compareLength = @lenLeft
      ELSE
        SET @compareLength = @lenRight

      SET @rightIndex = 1
      SET @leftIndex = 1

      WHILE @leftIndex <= @compareLength
        BEGIN
            SET @left_char = substring(@left, @leftIndex, 1)
            SET @right_char = substring(@right, @rightIndex, 1)

            IF @left_char <> @right_char
              BEGIN -- Would an insertion make them re-align?
                  IF( @left_char = substring(@right, @rightIndex + 1, 1) )
                    SET @rightIndex = @rightIndex + 1
                  -- Would an deletion make them re-align?
                  ELSE IF( substring(@left, @leftIndex + 1, 1) = @right_char )
                    SET @leftIndex = @leftIndex + 1

                  SET @difference = @difference + 1
              END

            SET @leftIndex = @leftIndex + 1
            SET @rightIndex = @rightIndex + 1
        END

      GOTO done

      DONE:

      RETURN @difference
  END 

然后您将通过以下方式将其添加到您的订单中

SELECT *
FROM [dbo].[some_table]
ORDER BY [dbo].[GetPercentageOfTwoStringMatching](col1 ,col2) DESC

使其适应您的 DTB，但这应该对您有用

或者您可以设置简单的 CASE WHEN 条件来创建订单列

SELECT *
FROM [dbo].[table]
ORDER BY
        CASE
          WHEN str = 'search_string' THEN 1
          WHEN str LIKE '%search_string%' THEN 2
          ELSE 3
        END