如何用and和or来编写containstable searchcondition [英] how to write containstable searchcondition with and's and or's

问题描述

正在尝试编写一个带有 containsstable 的select语句，并且搜索条件给了我一些伤心。 >目的是搜索具有3种不同参数类型的表格并相应地对输出进行排序。 （以下是我的代码示例）
$ b $ ol

关键字搜索条件（必须拥有并加权于1） || testvalue = Accountant

其他必备品（加权.8） || testvalue = Manager

不错（加权.5） || testvalue = Excel

SQL：

  SELECT KTBL.Rank as [Ranking]，KeySkills 
 FROM Applicants INNER JOIN 
 CONTAINSTABLE（Applicant，KeySkills，
'（ISABOUT（Accountantweight 1）））& 
（ISABOUT（经理权重（.8））））| 
（ISABOUT（Excel）权重（.5）））
 AS KTBL 
 ON Applicants.Id = KTBL。[KEY]; 
  

上述语句不会返回任何内容，我怀疑它是否可能在以上方式。

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

任何建议我可以做到这一点？

解决方案

我最终想出了一个解决我自己的问题，我很高兴暂时存在。

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

  CREATE PROCEDURE [dbo]。[GetJobSeekers] 
（
 @KeywordSearch nvarchar（500），
 @MustHave nvarchar（500）， - 逗号分隔的技能
 @NiceToHave nvarchar（500） - 逗号分隔技巧
）
 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）
 
 / *用于字符串分割* / 
 DECLARE @POS int 
 DECLARE @NEXTPOS int 
 
 / *获取主搜索键的结果* / 
 SET @SELECT ='选择ktbl.rank AS [排名]，求职者。*'
 SET @SEARCH ='ISABOUT（''+ @ KeywordSearch +'weight（1））'
 
 SET @WHERE ='FROM jobseekers INNER JOIN 
 CONTAINSTABLE求职者，*，'''+ @SEARCH +'''）
 AS ktbl on jobseekers.Id = ktbl。[KEY]'
 
 SET @ ORDERBY ='ORDER BY [Ranking] DESC'
 
 * *为所有其他必须设置的关键字和INNER JOIN设置结果使用主搜索* / 
 IF @MustHave<> ''
 
 BEGIN 
 DECLARE @MustHaveSplitString nvarchar（500）
 $ b $ SET SET @POS = 1 
 WHILE（@POS< = LEN（@MustHave ））
 BEGIN 
 SELECT @NEXTPOS = CHARINDEX（N'，'，@MustHave，@POS）
 IF（@NEXTPOS = 0或@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 
 
 
 / *获取结果集合，将它们堆叠在搜索条件和左外部加入主搜索+必须搜索其中是否有* / 
 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或@NEXTPOS IS （@NiceToHave，@POS，@NEXTPOS  -  @POS）））
 $（@NiceToHave）+ 1 
 SET @ SEARCH2 = @ SEARCH2 +''+ @ NiceToHaveSplitString +''weight（.5），'
 
 SELECT @POS = @ NEXTPOS + 1 
 END 
 / *清理最后一个，关闭search2字符串* / 
 SET @ SEARCH2 = LEFT（@ SEARCH2，LEN（@ SEARCH2）-1）
 / *关闭search2字符串中的isabout * / 
 SET @ SEARCH2 = @ SEARCH2 +'）'
 
 
 SET @SELECT = @SELECT +'，ktbl2.rank AS [Ranking2]'
 SET @ORDERBY = @ORDERBY + '，[Ranking2] DES C'
 
 SET @WHERE = @WHERE +'LEFT JOIN CONTAINSTABLE（jobseekers，*，'''+ @ SEARCH2 +'''）
在Jobseekers.Id = ktbl2上为ktbl2。 [KEY]'
 END 
 
 SET @SQL = @SELECT + @WHERE + @ORDERBY 
 
 EXEC sp_executesql @SQL 
 END 
  

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

亲切的问候，

Currently trying to write a select statement with a containstable and the search condition is giving me some grief.

The purpose is to search a table with 3 different parameter types and rank the output accordingly. (below is sample of my code)

1. Key search condition (must-have and weighted at 1) || testvalue = Accountant
2. Additional must-have (weighted .8) || testvalue = Manager
3. Nice to have (weighted .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];

The above statement does not return anything and I'm doubting whether its possible to stack isabout terms in the above manner.

The returned results must have both accountant AND manager and would be nice to have Excel

Any suggestions to have I can achieve this?

解决方案

I eventually came up with a solution to my own question that I'm happy with for the time being.

I ended up with the stored procedure as seen below here (a bit long winded) which takes 3 parameters 1. Primary Search Key (must match) 2. Additional Must Have comma separated string (must match) 3. Additional Nice to have comma separated string

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

The stored procedure is still not 100% complete as it needs to take additional logic into consideration but it will for the time being act as a shell for the end result. Also I have yet to test this against any substantial amount of data so I'm still unsure how well it will perform.

Kind regards,

这篇关于如何用and和or来编写containstable searchcondition的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！