选择满足动态表列出的要求的条目 [英] Select entries that fulfil requirements laid out by dynamic table

问题描述

我正在尝试进行关键字搜索，该关键字搜索要求满足所有条件才能显示结果.我创建了一种使用字符串创建自定义表格的方法，该字符串存储了此搜索当前所需的所有关键字.

I'm attempting to do a keyword search which requires all conditions to be met for a result to be shown. I've created a method of making a custom table from a string which stores all of the keywords which are currently required for this search.

我已经可以使用以下方法使它发生在或"中

I've been able to get it to happen for 'or' using the following

dbo.MultipleTextSearchValuesOR-用于制作关键字表

dbo.MultipleTextSearchValuesOR - Is used to make the table of keywords

select Title from vwIncidentSearchView inner join dbo.MultipleTextSearchValuesOR('Testing|Check') on Title Like id

这很好用，但似乎无法解决和"(例如，结果必须具有测试"和检查").

This works great but can't seem to work it out for 'and' (e.g. result must have 'Testing' and 'Check').

任何帮助将不胜感激

推荐答案

我知道了.必须完成的方法是使用交叉应用程序，该函数带有将字符串拆分为表的函数.

I've figured it out. The way it had to be done was using a cross apply with a function splitting the string into a table.

然后，必须按所有字段对条目进行分组.最后，必须检查它们是否符合所有必需的关键字.

Then the entries must be grouped by all the fields. Finally they must be checked to see if it meets all the required keywords.

我确实意识到这是一个非常麻烦的方法，但它并不是最优化的方法，因此，如果其他人对提高性能有任何建议，将不胜感激

I do realise this is quite a messy method along with it not beingthe most optimized method so if anyone else has any suggestions to improving performance it would be appreciated

示例:

declare @where nvarchar(max)
declare @TitleLikeClause nvarchar(max)
declare @Title nvarchar(max)

set @Title = 'Down&Email'

SELECT 
       Referenceid, 
       Title 
FROM vwIncidentSearchView  
cross apply
       (select 
             Data 
       from Split(@Title, '&') candidate 
       where 
             Title Like candidate.Data) t2o group by Referenceid, Title having count(*) = (select max(ID) from Split(@Title, '&'))

这篇关于选择满足动态表列出的要求的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！