尝试使用动态查询在大海捞针中搜索

Question

请考虑以下SQL Fiddle: http://sqlfiddle.com/#!3/9d4fc/3

我有三个文档:

('id1', 'lorem ipsum dolor dog'),
('id2', 'the cat jumps over the lazy dog'),
('id3', 'i have no clue what to write');

我想动态搜索这些文档中的针头:

('a', 'dog'),
('b', 'dolor'),
('c', 'write');

我需要的结果将是表tmp_auditlog_results看起来像

doc   needle  string
---------------------------------------------
id1   dog     lorem ipsum dolor dog
id2   dog     the cat jumps over the lazy dog
id1   dolor   lorem ipsum dolor dog
id3   write   i have no clue what to write

我被添加到结果表的动态查询弄糊涂了。你能看看我是如何从我现在的结果中得出这个结果的吗?

Answer 1

insert tmp_auditlog_results (doc,needle,string)
select doc, needles.needle, string 
from haystacks
inner join needles on haystacks.string like '%'+needles.needle+'%'

Answer 2

无需任何动态代码。您可以使用CHARINDEX

INSERT INTO tmp_auditlog_results
SELECT S.docid, F.NEEDLE 
FROM tmp_auditlog_subselection AS S
CROSS JOIN tmp_auditlog_fields AS F
WHERE CHARINDEX(F.needle, S.haystack) != 0

Answer 3

替换select语句:

select * from tmp_auditlog_fields;

和下面的语句:

SELECT DISTINCT 'id' + CAST(A.id as VARCHAR(9999)) AS doc, needle,  B.haystack 
FROM tmp_auditlog_fields A
INNER JOIN tmp_auditlog_subselection B 
ON B.docid =  'id'+ CAST(A.id as VARCHAR(9999))