具有全文索引的ContainsTable和布尔逻辑的多列 [英] Multiple columns with ContainsTable and boolean logic with full text index

问题描述

我有我认为的一个非常基本的场景，但是我读过的内容听起来像是使用SQL Server Full Text目录和索引很不容易。

<我有2列，名字和姓氏。我希望支持对他们进行全文搜索，这样，如果有人在上都匹配 Smith ，首先和最后出现在第一位。

尽管在多列中创建索引很容易，并且易于搜索多个列，但评分不会反映多列。 $ b

  SELECT [Key]，Rank 
从CONTAINSTABLE（[User]，（FirstName，LastName），'< CLAUSE_HERE>'）
  

$ b $ ol

如果CLAUSE_HERE是john smith，那么我不会得到任何结果，因为该字段在任何字段中都不存在。

如果是john or smith，那么我会在任一字段中获取具有任一名称的所有用户，并按照无用的顺序排序。

如果是john and smith ，因为两个字段都不包含两个单词。

似乎唯一的解决方案是自动生成运行 containstable 在每个领域，做一些数学，总结分数，等等这听起来正确吗？有更简单的方法吗？我的实际查询有更多的字段 - 这是一个简化的例子。 创建一个计算专栏，将您感兴趣搜索的字段混合在一起（以某种方式感觉你的搜索格式）和全文索引。

据我所知，这是唯一的工作，如果你想全文这是因为您在问题中描述的行为。

I have what I think is a very basic scenario, but what I've read makes it sound like this is not easy using SQL Server Full Text catalog and indexes.

I have 2 columns, First and Last name. I want to support full-text search on them such that if someone types "John Smith" people with a match on both first and last come up first.

Although it's easy to create an index across multiple columns, and easy to search multiple columns, the scoring doesn't reflect multiple columns.

SELECT [Key], Rank 
FROM CONTAINSTABLE([User], (FirstName,LastName), '<CLAUSE_HERE>')

1. If CLAUSE_HERE is "john smith" I get no results, because that phrase does not exist in either field.
2. If it's "john OR smith" I get all users with either name in either field, sorted in an unhelpful order.
3. If it's "john AND smith" I get no results, because neither field contains both words.

Seems like the only solution is to autogenerate a query that runs containstable on each field, does some math, sums the scores, etc. Does that sound right? Is there an easier way around it? My actual query has a lot more fields to it - this is a simplified example.

解决方案

Make a computed column which mushes together the fields that you're interested in searching on (in a way that makes sense for your search formats), and full-text index that.

As far as I'm aware this is the only work around if you want to full-text in this way because of the behavior you describe in your question.

这篇关于具有全文索引的ContainsTable和布尔逻辑的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！