SQL Server 2005 全文搜索多个表和列

Question

我正在寻找一个很好的解决方案来有效地使用 SQL Server r2005 的 containstable 功能。目前我有，例如一个雇员和一个地址表。

-Employee
Id
Name

-Address
Id
Street
City
EmployeeId

现在用户只能在一个文本框中输入搜索词，我希望将这些词拆分并使用“AND”运算符进行搜索。 FREETEXTTABLE 似乎可以自动使用“OR”。

现在假设用户输入了“John Hamburg”。这意味着他想在汉堡找到约翰。
所以这是“约翰和汉堡”。

因此，以下将不包含任何结果，因为 CONTAINSTABLE 会检查“John AND Hamburg”的每一列。

所以我的问题是:在多个列/表中使用 AND 运算符执行全文搜索的最佳方法是什么？

SELECT *
FROM Employee emp
    INNER JOIN 
        CONTAINSTABLE(Employee, *, '(JOHN  AND Hamburg)', 1000) AS keyTblSp
        ON sp.ServiceProviderId = keyTblSp.[KEY]    
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = emp.EmployeeId
UNION ALL
SELECT *
FROM Employee emp 
    LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = emp.EmployeeId
    INNER JOIN 
        CONTAINSTABLE([Address], *, '(JOHN  AND Hamburg)', 1000) AS keyTblAddr
        ON addr.AddressId = keyTblAddr.[KEY]    

...

Answer 1

这更像是一个语法问题。你如何通过一个输入框来判断用户的意图？

他们是在寻找“约翰汉堡”这个人吗？

他们在寻找“约翰汉堡街”吗？

他们是在寻找住在斯普林菲尔德“汉堡街”的“约翰”吗？

他们是在寻找住在“汉堡”市的“约翰”吗？

在不知道用户意图的情况下，您所能期望的最好结果就是对这些术语进行 OR 运算，并获得排名最高的点击量。

否则，您需要编写大量逻辑，具体取决于传入的字数:

2个字:

搜索词条 1 的员工数据，搜索词条 2 的员工数据，搜索词条 1 的地址数据，搜索词条 2 的地址数据。按词条合并结果，按点击次数排序。

3个字:

搜索词条 1 的员工数据，搜索词条 2 的员工数据，搜索词条 3 的员工数据，搜索词条 1 的地址数据，搜索词条 2 的地址数据，搜索词条 3 的地址数据。按词条合并结果，按最多排序命中。

等等...

我想我会重新设计 GUI，至少将输入分成名称和地址。如果这是不可能的，请强制执行一个语法规则，“在出现逗号之前，第一个单词将被视为名称，之后的任何单词将被视为地址”

编辑:

您最好的选择仍然是 OR 条款，并获得最高排名。这是一个例子，还有一个例子，为什么如果没有对输入进行一些预处理来判断用户的意图，这不是理想的:

insert into Employee (id, [name]) values (1, 'John Hamburg')
insert into Employee (id, [name]) values (2, 'John Smith')
insert into Employee (id, [name]) values (3, 'Bob Hamburg')
insert into Employee (id, [name]) values (4, 'Bob Smith')
insert into Employee (id, [name]) values (5, 'John Doe')

insert into Address (id, street, city, employeeid) values (1, 'Main St.', 'Springville', 1)
insert into Address (id, street, city, employeeid) values (2, 'Hamburg St.', 'Springville', 2)
insert into Address (id, street, city, employeeid) values (3, 'St. John Ave.', 'Springville', 3)
insert into Address (id, street, city, employeeid) values (4, '5th Ave.', 'Hamburg', 4)
insert into Address (id, street, city, employeeid) values (5, 'Oak Lane', 'Hamburg', 5)

现在因为我们不知道哪些关键字将应用于哪个表，我们必须假设它们可以应用于任何一个表，所以我们必须对每个表进行 OR 项，UNION 结果，聚合它们，并计算最高排名。

SELECT Id, [Name], Street, City, SUM([Rank])
FROM
(
    SELECT emp.Id, [Name], Street, City, [Rank]
    FROM Employee emp 
    JOIN [Address] addr ON emp.Id = addr.EmployeeId
    JOIN CONTAINSTABLE(Employee, *, 'JOHN OR Hamburg') AS keyTblEmp ON emp.Id = keyTblEmp.[KEY]

    UNION ALL

    SELECT emp.Id, [Name], Street, City, [Rank]
    FROM Employee emp 
    JOIN [Address] addr ON emp.Id = addr.EmployeeId
    JOIN CONTAINSTABLE([Address], *, 'JOHN OR Hamburg') AS keyTblAdd ON addr.Id = keyTblAdd.[KEY]   
) as tmp

GROUP BY Id, [Name], Street, City
ORDER BY SUM([Rank]) DESC

这不太理想，这是您从示例中获得的内容(在您的情况下，您会希望来自汉堡的 John Doe 首先出现):

Id       Name              Street            City           Rank
2        John Smith        Hamburg St.       Springville    112
3        Bob Hamburg       St. John Ave.     Springville    112
5        John Doe          Oak Lane          Hamburg        96
1        John Hamburg      Main St.          Springville    48
4        Bob Smith         5th Ave.          Hamburg        48

但是，在将输入提交给 SQL 以对用户想要的内容进行“最佳猜测”之前，无需解析输入，这是最好的做法。