sql - SQL全文搜索结果优先级

我曾经使用Lucene进行全文搜索，但是效果很好，但是我的主要数据库是SQL。我不喜欢为数据库提供两种机制的想法，因此决定使用SQL全文搜索。一切正常，但有些事情我还没有弄清楚。
可以说我有下表:



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

现在，我要在此表上使用文本“Isaac”进行全文搜索。我希望第5行位于顶部，而其余结果则低于该行。
我可以为字段设置优先级，以便在该字段上找到的任何内容都位于结果的顶部吗？基本上，我想按领域优先处理。在我的真实表格中，我有6个字段。

最佳答案

据我所知，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

关于sql - SQL全文搜索结果优先级，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36492073/