SQL Server全文搜索 - 创建一个计算列 [英] SQL Server Full Text Search - Create one computed column
问题描述
我目前正在研究一个项目，我只想通过一个输入搜索词搜索员工。为此，我使用SQL FTS。
表模式如下所示：
员工表
$ b EmployeeId，名字，姓氏
示例数据 p>
<1>，John，Miller
2，Chuck，Norris
地址表
AddressId，EmployeeId，CityId，Street，StreetNumber
样本数据
<1>，1，Avenue，12
$ b 2,2,2温布尔登路12，
$ hr
$ b
城市表格
CityId，Name，ZipCode
示例数据
<1>，汉堡22335
2，伦敦12345
所以现在我得到了以下搜索词：
- John Hamburg：意思是约翰和汉堡，并应该返回1条记录。
- 约翰伦敦：意味着约翰和隆多n，并且应该返回0条记录，因为在伦敦没有约翰。
- Norris Wimbledon：意味着Norris和Wimbledone，并且应该返回1条记录。
现在问题在于使用CONTAINSTABLE只允许一次搜索一个表。因此，在员工全文目录中应用John AND Hamburg后，由于汉堡位于地址表中，因此将返回0条记录。
因此，目前我可以使用OR而不是AND，如：
SELECT
（keyTblSp.RANK * 3）AS [Rank]，
sp。*
FROM员工sp
内部加入
CONTAINSTABLE（员工，*，'John OR Hamburg'，1000）AS keyTblSp
ON sp.EmployeeId = keyTblSp 。[KEY]
UNION ALL
SELECT
（keyTbl.RANK * 2）AS [Rank]，
sp。*
FROM员工sp
LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId
INNER JOIN
CONTAINSTABLE（[Address]，*，'John OR Hamburg'，1000）AS keyTbl
ON addr.AddressId = keyTbl。[KEY]
UNION ALL
SELECT
（keyTbl.RANK * 2）AS [Rank]，
sp。*
FROM Employee sp
LEFT OUTER JOIN [地址] addr ON addr.EmployeeId = sp.EmployeeId
L EFT OUTER JOIN [City] cty ON cty.CityId = addr.CityId
INNER JOIN
CONTAINSTABLE（[City]，*，'John OR Hamburg'，1000）AS keyTbl
ON cty。 CityId = keyTbl。[KEY]
这会导致不仅仅是返回汉堡的John，名叫约翰的人和住在汉堡的每一个人。
我能想到的一个解决方案是以某种方式计算员工表中的一列，其中包含全文搜索的所有必要值。
雇员表
EmployeeId，名字，姓氏， FulltextColumn
示例数据
1 |约翰|米勒| John Miller Avenue 12汉堡22335
然后我可以做
pre >SELECT
（keyTbl.RANK）AS [Rank]，
sp。*
FROM员工sp
INNER JOIN
CONTAINSTABLE（[Employee ]，FulltextColumn，'John AND Hamburg'，1000）AS keyTbl
ON sp.EmployeeId = keyTbl。[KEY]
这可能吗？任何其他想法？
解决方案您可以使用连接来要求地址和人员名称匹配。
SELECT
（keyTblSp.RANK * 3）AS [Rank]，
sp。*
FROM员工sp
INNER JOIN
CONTAINSTABLE（Employee，*，'John OR Hamburg'，1000）AS keyTblSp
ON sp.EmployeeId = keyTblSp。[KEY]
join
（
SELECT
（keyTbl.RANK * 2）AS [Rank]，
sp。*
FROM员工sp
LEFT OUTER JOIN [地址] addr ON addr .EmployeeId = sp.EmployeeId
INNER JOIN
CONTAINSTABLE（[Address]，*，'John OR Hamburg'，1000）AS keyTbl
ON addr.AddressId = keyTbl。[KEY]
UNION ALL
SELECT
（keyTbl.RANK * 2）AS [Rank]，
sp。*
FROM员工sp
LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId
LEFT OUTER JOIN [City] cty ON cty .CityId = addr.CityId
INNER JOIN
CONTAINSTABLE（[City]，*，'John OR Hamburg'，1000）AS keyTbl
ON cty.CityId = keyTbl。[KEY]
）addr_matches
on addr_matches.EmployeeId = sp.EmployeeId
我认为会给你明确了你所指定的结果，但是这要求搜索的名称和地址搜索条件都返回任何结果。你没有说明如果有人只是搜索'约翰'，如果你总是会得到一个姓名和地址，会发生什么事情，我认为上述将工作正常。
I am currently working on a project where I want to search for employees via just one input search term. For this I am using the SQL FTS.
The table schema looks like this
Employee table
EmployeeId, Firstname, Lastname
Sample data
1, John, Miller
2, Chuck, Norris
Address table
AddressId, EmployeeId, CityId, Street, StreetNumber
Sample data
1, 1, 1, Avenue, 12
2, 2, 2, Wimbledon Rd, 12
City table
CityId, Name, ZipCode
Sample data
1, Hamburg, 22335
2, London, 12345
So now I got the following search term:
- John Hamburg: Means John AND Hamburg and should return 1 record.
- John London: Means John AND London and should return 0 records since there is no John in London.
- Norris Wimbledon: Means Norris AND Wimbledone and should return 1 records.
Now the problem with this is that using CONTAINSTABLE only allows to search one table at a time. So applying "John AND Hamburg" on the Employee Full text catalog returns 0 records since "Hamburg" is located in the address table.
So currently I can use "OR" instead of "AND" only, like:
SELECT (keyTblSp.RANK * 3) AS [Rank], sp.* FROM Employee sp INNER JOIN CONTAINSTABLE(Employee, *, 'John OR Hamburg', 1000) AS keyTblSp ON sp.EmployeeId = keyTblSp.[KEY] UNION ALL SELECT (keyTbl.RANK * 2) AS [Rank], sp.* FROM Employee sp LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId INNER JOIN CONTAINSTABLE([Address], *, 'John OR Hamburg', 1000) AS keyTbl ON addr.AddressId = keyTbl.[KEY] UNION ALL SELECT (keyTbl.RANK * 2) AS [Rank], sp.* FROM Employee sp LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId LEFT OUTER JOIN [City] cty ON cty.CityId = addr.CityId INNER JOIN CONTAINSTABLE([City], *, 'John OR Hamburg', 1000) AS keyTbl ON cty.CityId = keyTbl.[KEY]This causes that not just John who lives Hamburg is returned, but every person named John and every person who lives in Hamburg. One solution I could think of is to somehow compute a column in the Employee Table that holds all necessary values for the full text search like.
Employee table
EmployeeId, Firstname, Lastname, FulltextColumn
Sample data
1 | John | Miller | John Miller Avenue 12 Hamburg 22335
So then I could do
SELECT (keyTbl.RANK) AS [Rank], sp.* FROM Employee sp INNER JOIN CONTAINSTABLE([Employee], FulltextColumn, 'John AND Hamburg', 1000) AS keyTbl ON sp.EmployeeId = keyTbl.[KEY]Is this possible? Any other ideas?
解决方案you could use a join to require a match in both the address and the persons name.
SELECT (keyTblSp.RANK * 3) AS [Rank], sp.* FROM Employee sp INNER JOIN CONTAINSTABLE(Employee, *, 'John OR Hamburg', 1000) AS keyTblSp ON sp.EmployeeId = keyTblSp.[KEY] join ( SELECT (keyTbl.RANK * 2) AS [Rank], sp.* FROM Employee sp LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId INNER JOIN CONTAINSTABLE([Address], *, 'John OR Hamburg', 1000) AS keyTbl ON addr.AddressId = keyTbl.[KEY] UNION ALL SELECT (keyTbl.RANK * 2) AS [Rank], sp.* FROM Employee sp LEFT OUTER JOIN [Address] addr ON addr.EmployeeId = sp.EmployeeId LEFT OUTER JOIN [City] cty ON cty.CityId = addr.CityId INNER JOIN CONTAINSTABLE([City], *, 'John OR Hamburg', 1000) AS keyTbl ON cty.CityId = keyTbl.[KEY] ) addr_matches on addr_matches.EmployeeId = sp.EmployeeIdwhich I think would give you the results you specified, obviously though, this requires both a name and an address search term for a search to return any results. You didn't specify what happens if someone just searches for 'John', if you will always get both a name and address the above will work fine I think.
这篇关于SQL Server全文搜索 - 创建一个计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
