问题描述
正在尝试编写一个带有 containsstable
的select语句,并且搜索条件给了我一些伤心。 >目的是搜索具有3种不同参数类型的表格并相应地对输出进行排序。 (以下是我的代码示例)
$ b $ ol
|| testvalue = Accountant
|| testvalue = Manager
|| 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)
- Key search condition (must-have and weighted at 1)
|| testvalue = Accountant
- Additional must-have (weighted .8)
|| testvalue = Manager
- 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 parameters1. 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!