时间戳

首页 > 解决方案 > 修改棘手的外部 SQL 查询

sql-server - 修改棘手的外部 SQL 查询

问题描述

我有兴趣修改外部查询以非常具体的方式扩大查询结果。

我的内部查询创建了一个表。外部查询创建从表中删除记录的条件。我希望外部查询删除的是其中单词“not”与两个查询中的短语（“%postterm%”、“%post__term%”、“after__term%”等）包含在同一个句子中的记录。 ..)

到目前为止，外部查询只删除了以“not”结尾的短语前两个字符的记录。

“not”可以出现在这个短语之前或之后。我需要的条件是句号之间是否包含“不”。

任何帮助，将不胜感激！干杯。

SELECT sub.*
FROM(
    SELECT TOP 10000 *
    FROM MyTable N
    WHERE

  (N.[Subject] like '%postterm%')
  OR
  (N.[Content] like '%postterm%')
  ORpos
  (N.[Subject] like '%post_term%')
  OR
  (N.[Content] like '%post_term%')
  Or
  (N.[Subject] like '%post__term%')
  OR
  (N.[Content] like '%post__term%')
  OR
  (N.[Subject] like '%afterterm%')
  OR
  (N.[Content] like '%afterterm%')
  OR
  (N.[Subject] like '%after_term%')
  OR
  (N.[Content] like '%after_term%')
  Or
  (N.[Subject] like '%after__term%')
  OR
  (N.[Content] like '%after__term%')

) sub

WHERE

  (sub.[Subject] not like '%not_postterm%'

    )
      OR

  (sub.[Content] not like '%not_postterm%')
  OR
  (sub.[Subject] not like '%not_post_term%')
  OR
  (sub.[Content] not like '%not_post_term%')
  Or
  (sub.[Subject] not like '%not_post__term%')
  OR
  (sub.[Content] not like '%not_post__term%')
  OR
  (sub.[Subject] not like '%not_afterterm%')
  OR
  (sub.[Content] not like '%not_afterterm%')
  OR
  (sub.[Subject] not like '%not_after_term%')
  OR
  (sub.[Content] not like '%not_after_term%')
  Or
  (sub.[Subject] not like '%not_after__term%')
  OR
  (sub.[Content] not like '%not_after__term%')

标签： sql-server

解决方案

这一切都可以在一个更聪明的查询中完成。String_Split函数需要Sql Server 2012或更高版本，否则请查看如何创建自己的函数。

-- Set up the content
declare @T table(ID int, Content varchar(1000))
insert @t values (1, 'Hello. This is not Post Term. Another sentence')
insert @t values (2, 'Another. This is Post Term. Another sentence 2')

-- Define the search terms
declare @check table(Term varchar(100))
insert @check values ('%postterm%'),('%post_term%'),('%post__term%'),('%afterterm%'),('%after_term%'),('%after__term%')

-- This query shows you how how it works
SELECT ID, Content, SPL.value, Term, 
    case when value like '% NOT %' then 1 else 0 end as HasNot
FROM @T  AS PP
CROSS APPLY STRING_SPLIT(Content,'.') AS SPL
left join @check on SPL.Value like term

-- Final result
; with results as (
    SELECT ID, Content, SPL.value, Term, 
        case when value like '% NOT %' then 1 else 0 end as HasNot
    FROM @T  AS PP
    CROSS APPLY STRING_SPLIT(Content,'.') AS SPL
    left join @check on SPL.Value like term
)
select distinct ID,Content 
from results 
where term is not null and HasNot=1

推荐阅读