在全文搜索中，我不明白“不” [英] I dont' understand with “not” in full text search

问题描述

我不明白为什么我会在SQL Server 2014中使用全文搜索查询来获取这些结果。

以下查询返回Supervisor 在标题字段以及 HTML_Description 字段中的东京。

  SELECT 
 * 
 FROM post 
 JOIN CONTAINSTABLE（[post]，（Title，HTML_Description），'Supervisor AND NOT Tokyo'） 
 AS tb1 ON tb1。[Key] = post.ID 
  

如果我删除 HTML_Description 字段位于 CONTAINSTABLE 中，如下所示：

  SELECT 
 * 
 FROM post 
 JOIN CONTAINSTABLE（post，（Title），'Supervisor AND NOT Tokyo'）
 AS tb1 ON tb1。[Key ] = post.ID 
  

结果是一样的。

如果我在 CONTAINSTABLE 中仅使用 HTML_Description 字段，如下所示：

  SELECT 
 * 
 FROM post 
 JOIN CONTAINSTABLE（post，（HTML_Description），'Supervisor AND NOT Tokyo'）
 AS tb1 ON tb1。[Key] = post.ID 
  

没有任何结果。

如何应用 NOT 运算符到所有字段？

解决方案

？因为即使在跨多个列进行搜索时，全文搜索条件也必须满足在单个列中 。因此，您的查询等同于：

  SELECT 
 * 
 FROM 
 JOIN CONTAINSTABLE（ （职位），（职位），'主管和非东京'）
 AS tb1 ON tb1。[Key] = post.ID 
 UNION ALL 
 SELECT 
 * 
 FROM post 
 JOIN CONTAINSTABLE（[post]，（HTML_Description），'Supervisor AND NOT Tokyo'）
 AS tb1 ON tb1。[Key] = post.ID 
  

解决此问题的方法：

您应该将标题和 HTML_Description 结合到一个列中（您甚至可以创建一个计算列来完成此操作）并创建完整值得一提的是：在大多数情况下，您可以使用2个或更多的 CONTAINSTABLE 来重写查询。

或 CONTAINS 语句来分别搜索每个关键字 - CONTAINS（*，'apples'）和CONTAINS（*，'oranges'）而不是 CONTAINS（*，'苹果和ora nges'） - 但是你会得到多个无意义的 Rank 列（因为每个列都是针对单个关键字计算的） t完全可以获得 Rank （如果您使用 CONTAINS ）。下面的链接显示了一个简单的例子。但是，这不适用于你的情况，因为你使用了NOT。您不能仅使用NOT条件执行全文搜索 - CONTAINS（*，'NOT Tokyo'）会引发错误。

请参见使用CONTAINS子句搜索查询全列

I don't understand why I'm getting these results with my full-text search query in SQL Server 2014.

The following query returns results with "Supervisor" in the Title field and "Tokyo" in the HTML_Description field.

SELECT 
    * 
FROM post
    JOIN CONTAINSTABLE([post], (Title, HTML_Description), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

If I remove the HTML_Description field in the CONTAINSTABLE like this:

SELECT 
    * 
FROM post 
    JOIN CONTAINSTABLE(post, (Title), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

The result is the same.

If I use only the HTML_Description field in the CONTAINSTABLE like this:

SELECT 
    * 
FROM post 
    JOIN CONTAINSTABLE(post, (HTML_Description), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

There aren't any results.

How can I apply the NOT operator to all fields?

解决方案

Why does this happen? Because even when searching across multiple columns, the full text search conditions must be satisfied within a single column. Thus your query is equivalent to:

SELECT 
    * 
FROM post
    JOIN CONTAINSTABLE([post], (Title), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID
UNION ALL
SELECT 
    * 
FROM post
    JOIN CONTAINSTABLE([post], (HTML_Description), 'Supervisor AND NOT Tokyo') 
        AS tb1 ON tb1.[Key] = post.ID

Ways to work around this:

You should combine Title and HTML_Description into a single column (you can even create a computed column to do this) and create the full text index on this column.

Worth mentioning: In most cases you can rewrite the query using 2 or more CONTAINSTABLE or CONTAINS statements to search each keyword individually -- CONTAINS(*, 'apples') and CONTAINS(*, 'oranges') instead of CONTAINS(*, 'apples AND oranges') -- but then you'll either get multiple meaningless Rank columns (because each is calculated against a single keyword) or you won't get a Rank at all (if you use CONTAINS). The link below shows a simple example of this. However this doesn't apply to your case because of your use of NOT. You cannot perform a full text search using only a NOT condition -- CONTAINS(*, 'NOT Tokyo') will throw an error.

See Full-Text Search Queries with CONTAINS Clause Search Across Columns

这篇关于在全文搜索中，我不明白“不”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！