如何用and的and or编写containstable搜索条件

Question

当前试图用a编写select语句，containstable并且搜索条件使我有些悲痛。

目的是搜索具有3种不同参数类型的表，并相应地对输出进行排名。（下面是我的代码示例）

1. 关键搜索条件（必须且加权为1） || testvalue = Accountant
2. 其他必备（加权.8） || testvalue = Manager
3. 很高兴（加权.5） || testvalue = Excel

SQL：

 SELECT KTBL.Rank as [Ranking], KeySkills
   FROM Applicants INNER JOIN 
      CONTAINSTABLE(Applicants, KeySkills, 
        '(ISABOUT("Accountant" weight(1))) & 
         (ISABOUT("Manager" weight(.8))) | 
         (ISABOUT("Excel") weight(.5)))
     AS KTBL
   ON Applicants.Id = KTBL.[KEY];

上面的语句不返回任何内容，我怀疑它是否有可能以上述方式堆叠术语。

返回的结果必须同时拥有会计和经理，并且很高兴拥有Excel

有什么建议可以实现这一目标吗？

Answer 1

我最终想出了一个对自己的问题的解决方案，我暂时对此感到满意。

我结束了存储过程，如下所示（有点冗长），它需要3个参数1.主搜索键（必须匹配）2.附加的必须用逗号分隔字符串（必须匹配）3.附加的要用逗号分隔细绳

CREATE PROCEDURE [dbo].[GetJobSeekers]
(
@KeywordSearch nvarchar(500),
@MustHave nvarchar(500), --Comma separated skills
@NiceToHave nvarchar(500) --Comma separated skills
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SQL nvarchar(4000)
DECLARE @SELECT nvarchar(4000)
DECLARE @SEARCH nvarchar(4000)
DECLARE @SEARCH1 nvarchar(4000)
DECLARE @SEARCH2 nvarchar(4000)
DECLARE @WHERE nvarchar(4000)
DECLARE @ORDERBY nvarchar(4000)

/*Used for string split*/
DECLARE @POS int
DECLARE @NEXTPOS int

/*Get Result for primary search key*/
SET @SELECT =   'SELECT ktbl.rank AS [Ranking], jobseekers.*'
SET @SEARCH =   'ISABOUT("'+@KeywordSearch+'" weight(1))'

SET @WHERE  =   ' FROM jobseekers INNER JOIN
                CONTAINSTABLE (jobseekers, *, ''' + @SEARCH + ''')
                AS ktbl On jobseekers.Id = ktbl.[KEY]'

SET @ORDERBY=   'ORDER BY [Ranking] DESC'

/* Get Result set for all additional must have keywords and INNER JOIN With primary Search */
IF @MustHave <> ''

BEGIN
    DECLARE @MustHaveSplitString nvarchar(500)

    SET @POS = 1
    WHILE(@POS <= LEN(@MustHave))
    BEGIN
        SELECT @NEXTPOS = CHARINDEX(N',', @MustHave,  @POS)
        IF (@NEXTPOS = 0 OR @NEXTPOS IS NULL)
              SELECT @NEXTPOS = LEN(@MustHave) + 1
        SELECT @MustHaveSplitString = RTRIM(LTRIM(SUBSTRING(@MustHave, @POS, @NEXTPOS - @POS)))

        SET @SELECT = @SELECT + ', ktbl'+@MustHaveSplitString+'.rank AS [Ranking'+@MustHaveSplitString+']'
        SET @ORDERBY = @ORDERBY + ', [Ranking'+@MustHaveSplitString+'] DESC'
        SET @SEARCH1 = 'ISABOUT("'+@MustHaveSplitString+'" weight(.8))'
        SET @WHERE = @WHERE + ' INNER JOIN CONTAINSTABLE (jobseekers, *, ''' + @SEARCH1 + ''')
                                AS ktbl'+@MustHaveSplitString+' on Jobseekers.Id = ktbl'+@MustHaveSplitString+'.[KEY]'
        SELECT @POS = @NEXTPOS+1    
    END 
END


/*Get result set for all nice to have by stacking them in the isabout searchcondition and LEFT OUTER JOIN with Primary Search + Must have search if its there*/
IF @NiceToHave <> ''
BEGIN
    DECLARE @NiceToHaveSplitString nvarchar(500)        
    SET @SEARCH2 = 'ISABOUT('

    SET @POS = 1
    WHILE(@POS <= LEN(@NiceToHave))
    BEGIN
        SELECT @NEXTPOS = CHARINDEX(N',', @NiceToHave,  @POS)
        IF (@NEXTPOS = 0 OR @NEXTPOS IS NULL)
              SELECT @NEXTPOS = LEN(@NiceToHave) + 1
        SELECT @NiceToHaveSplitString = RTRIM(LTRIM(SUBSTRING(@NiceToHave, @POS, @NEXTPOS - @POS)))

        SET @SEARCH2 = @SEARCH2 + '"'+@NiceToHaveSplitString+'" weight(.5),'

        SELECT @POS = @NEXTPOS+1    
    END         
    /*Clean last , off the search2 string */
    SET @SEARCH2 = LEFT(@SEARCH2, LEN(@SEARCH2) -1)
    /*Close the isabout in search2 string*/
    SET @SEARCH2 = @SEARCH2 + ')'


    SET @SELECT = @SELECT + ', ktbl2.rank AS [Ranking2]'
    SET @ORDERBY = @ORDERBY + ', [Ranking2] DESC'

    SET @WHERE = @WHERE + ' LEFT JOIN CONTAINSTABLE (jobseekers, *, ''' + @SEARCH2 + ''')
                            AS ktbl2 on Jobseekers.Id =          ktbl2.[KEY]'
END

SET @SQL    =   @SELECT + @WHERE + @ORDERBY

EXEC sp_executesql @SQL
END

存储过程仍未100％完成，因为它需要考虑其他逻辑，但是暂时将充当最终结果的外壳。另外，我还没有针对任何大量数据进行测试，因此我仍然不确定它的性能如何。

亲切的问候，