如何對多個表使用 freetexttable？

我有一個數據庫，其中（除其他外）包含支持票證的表格。每張票都有一個簡短的摘要和說明。每個工單可以有多個註釋，每個註釋都有一個包含詳細資訊的關聯操作。除此之外，工單還可以有一個 ProblemAndSolution，這是一個標準的問題和解決方案。

這個（削減）模式看起來像這樣……

create table ProblemAndSolutions (
ID int not null identity(1,1) primary key,
Problem varchar(max) not null default '',
Solution varchar(max) not null default '',
)
go

create table Actions (
ID int not null identity(1,1) primary key,
Details varchar(max) not null default '',
)
go

create table SupportTickets (
ID int not null identity(1,1) primary key,
ShortSummary varchar(max) not null default '',
Description varchar(max) not null default '',
ProblemAndSolutionID int foreign key references ProblemAndSolutions(ID),
)
go

create table SupportTicketNotes (
ID int not null identity(1,1) primary key,
SupportTicketID int not null foreign key references SupportTickets(ID),
ActionID int not null foreign key references Actions(ID),
)
go

我被要求提供一個功能，使用者可以輸入關鍵字，並將顯示相關票證，其中上表中的所有 varchar 欄位都將被搜尋。換句話說，他們希望看到關鍵字出現在工單簡短摘要或描述中，和/或問題和/或解決方案中，和/或註釋的 Action.Details 欄位中的工單。顯然，出現的次數越多，等級就越高。

這似乎是 freetexttable 的理想工作，而且確實在一張桌子上，這很好用。但是，我很難了解如何在所有表中進行搜尋。我已經看到了很多關於創建連接的答案，但我看不出這會有什麼幫助，因為如果關鍵字出現在多個 ProblemAndSolutions 和註釋/操作中，您最終會為每張工單獲得多個結果。

我想要的是最終得到一組結果，其中每張票只出現一次，其排名基於在上面顯示的任何 varchar 欄位中找到的關鍵字。

任何人都能夠解釋我如何做到這一點？

編輯為了更清楚，這裡有一些（即時組成，因此是愚蠢的）範例數據……

票#1

ShortSummary = “電腦壞了”

描述 = “電腦壞了”

連結到 P&S #1（見下文）

有一個註釋，其 Action.Details 包含一次“電腦”一詞

票#2

ShortSummary = “損壞的系統”

描述=“電腦系統壞了”

未連結到 P&S

有一個註釋，其 Action.Details 包含兩次“電腦”一詞

P&S #1

問題=“電腦不工作”

解決方案=“電腦壞了，需要更換”

現在，工單#1 五次使用“電腦”一詞，一次​​在摘要和描述中，一次在註釋中，兩次在連結的 P&S 中。Ticket #2 三次使用“電腦”一詞，一次​​在描述中，兩次在註釋中。

我希望在“電腦”一詞上進行搜尋，因為關鍵字出現的次數更多，所以將票 #1 排在 #2 之上。

編輯#2

為了更容易嘗試，這裡有一些用於生成範例數據的 SQL。這和我上面展示的樣本不完全一樣，但原理是一樣的……

insert ProblemAndSolutions (Problem, Solution) values ('computer not working', 'computer broken')
insert ProblemAndSolutions (Problem, Solution) values ('unclear error report', 'stupid customer, tell them to buy a new computer')
insert ProblemAndSolutions (Problem, Solution) values ('crashed computer', 'the computer is a broken computer')

insert Actions (Details) values ('we checked it and id did not work when we tried to start it')
insert Actions (Details) values ('the computer is completely broken and needs replacing')
insert Actions (Details) values ('we called, but the customer was out. will call back again')
insert Actions (Details) values ('brand new computer, so probably not a fault. more likely a stupid customer')
insert Actions (Details) values ('customer reported something going wrong, but we couldn''t find anything')

insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('computer broken', 'the computer is completely broken and is not working', 1)
insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('broken computer', 'we have broken the pc today', NULL)
insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('computer crash', 'the machine crashed when we tried to start it', 2)
insert SupportTickets (ShortSummary, Description, ProblemAndSolutionID) values ('computer crash computer', 'the compter crashed when we tried to start the computer', 3)

insert SupportTicketNotes (SupportTicketID, ActionID) values (1, 1)
insert SupportTicketNotes (SupportTicketID, ActionID) values (1, 2)
insert SupportTicketNotes (SupportTicketID, ActionID) values (2, 3)
insert SupportTicketNotes (SupportTicketID, ActionID) values (2, 4)
insert SupportTicketNotes (SupportTicketID, ActionID) values (3, 5)

您有兩種選擇：

1. 在每個表上創建單獨的全文索引並將單獨的查詢結果合併。您必須使用自己的元排名標準，因為不同全文索引的排名不具有可比性。這可能會導致相當複雜和嵌套的查詢來實現所需的結果。
2. 創建另一個表，其中包含您的工單的所有可搜尋欄位，並在該表上創建一個全文索引。您可以將所有文本合併到一列，也可以在每個表中使用不同的列，這取決於您的搜尋選項以及您希望如何進行排名。這允許您在所有相關數據中使用 FreeTextTable 的固有排名功能。此選項需要更多努力來維護每張票的可搜尋文本，但查詢起來會容易得多，並且根據您的排名標準，排名可能更一致和可靠。

如果您正在尋找特定的關鍵字，您應該考慮使用 CONTAINSTABLE 而不是 FREETEXTTABLE，後者旨在匹配前者的含義與確切的單詞。

引用自：https://dba.stackexchange.com/questions/124472