Использование полнотекстового поиска в SQL Server 2008 для нескольких таблиц, столбцов

Мне нужно выполнить поиск по нескольким столбцам из двух таблиц в моей базе данных, используя полнотекстовый поиск. Эти две таблицы содержат соответствующие столбцы с полным текстом.

Причина, по которой я выбираю полнотекстовый поиск: 1. Чтобы легко находить акцентированные слова (кафе) 2. Уметь оценивать по близости слова и т.д. 3. "Вы имели в виду XXX?" Функциональность

Вот структура таблицы фиктивная, чтобы проиллюстрировать проблему:

Table Book
BookID
Name (Full-text indexed)
Notes (Full-text indexed)

Table Shelf
ShelfID
BookID

Table ShelfAuthor
AuthorID
ShelfID

Table Author
AuthorID
Name (Full-text indexed)

Мне нужно выполнить поиск по имени книги, книжным заметкам и имени автора.

Я знаю два способа сделать это:

Использование полнотекстового индексированного представления:. Это был мой предпочтительный метод, но я не могу этого сделать, потому что для того, чтобы представление было полнотекстовым, оно должно быть schemabound, не иметь никаких внешних соединений, иметь уникальный индекс. Представление, которое мне нужно будет для получения моих данных, не удовлетворяет этим ограничениям (оно содержит много других связанных таблиц, из которых мне нужно получить данные).
Использование объединений в хранимой процедуре. Проблема с этим подходом заключается в том, что мне нужно, чтобы результаты сортировались по рангу. Если я делаю несколько объединений по таблицам, SQL Server не будет искать по нескольким полям по умолчанию. Я могу комбинировать два отдельных запроса CONTAINS по двум связанным таблицам, но я не знаю, как извлечь ранг в сочетании из двух поисковых запросов. Например, если я ищу "Артур", результаты как запроса книги, так и запроса автора должны быть приняты во внимание и взвешены соответственно.

Ответ 1

Используя FREETEXTTABLE, вам просто нужно разработать некоторый алгоритм для вычисления объединенного ранга по каждому результату объединенной таблицы. Приведенный ниже пример перекосит результат в сторону хитов из таблицы книг.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

Обратите внимание, что я упростил вашу схему для этого примера.

Ответ 2

Я не думаю, что принятый ответ решит проблему. Если вы попытаетесь найти все книги у определенного автора и, следовательно, используйте имя автора (или его часть) в качестве критериев поиска, единственными книгами, возвращаемыми запросом, будут те, которые имеют критерии поиска от своего имени.

Единственное, что я вижу вокруг этой проблемы, - это копировать столбцы Author, которые вы хотите искать в таблице Book, и индексировать эти столбцы (или столбцы, поскольку, вероятно, было бы разумно хранить соответствующую информацию автора в столбце XML в Книжной таблице).

Ответ 3

У меня была такая же проблема, как у вас, но на самом деле она включала в себя 10 таблиц (таблица "Пользователи" и несколько других сведений)

Я сделал свой первый запрос, используя FREETEXT в предложении WHERE для каждой таблицы, но запрос был слишком длинным.

Затем я увидел несколько ответов об использовании FREETEXTTABLE и проверке значений не нулей в столбце ключей для каждой таблицы, но это также потребовало длительного выполнения.

Я исправил его, используя комбинацию FREETEXTTABLE и UNION:

SELECT Users.* FROM Users INNER JOIN
(SELECT Users.UserId FROM Users INNER JOIN FREETEXTTABLE(Users, (column1, column2), @variableWithSearchTerm) UsersFT ON Users.UserId = UsersFT.key
UNION
SELECT Table1.UserId FROM Table1 INNER JOIN FREETEXTTABLE(Table1, TextColumn, @variableWithSearchTerm) Table1FT ON Table1.UserId = Table1FT.key
UNION
SELECT Table2.UserId FROM Table2 INNER JOIN FREETEXTTABLE(Table2, TextColumn, @variableWithSearchTerm) Table2FT ON Table2.UserId = Table2FT.key
... --same for all tables
) fts ON Users.UserId = fts.UserId

Это оказалось невероятно быстрым.

Надеюсь, это поможет.

Ответ 4

Я бы использовал хранимую процедуру. Полнотекстовый метод или что-то другое возвращает ранг, который вы можете сортировать. Я не уверен, как они будут взвешены против друг друга, но я уверен, что вы можете немного поработать и понять это. Например:

Select SearchResults.key, SearchResults.rank From FREETEXTTABLE(myColumn, *, @searchString) as SearchResults Order By SearchResults.rank Desc

Ответ 5

FWIW, в аналогичной ситуации наш DBA создал триггеры DML для поддержки выделенной полнотекстовой таблицы поиска. Из-за его многочисленных ограничений использовать материализованное представление невозможно.

Ответ 6

Этот ответ просрочен, но один из способов сделать это, если вы не можете изменить первичные таблицы, - это создать новую таблицу с параметрами поиска, добавленными в один столбец.

Затем создайте полный текстовый индекс в этом столбце и запросите этот столбец.

Пример

SELECT 
    FT_TBL.[EANHotelID]                 AS HotelID, 
    ISNULL(FT_TBL.[Name],'-')           AS HotelName,
    ISNULL(FT_TBL.[Address1],'-')       AS HotelAddress,
    ISNULL(FT_TBL.[City],'-')           AS HotelCity,
    ISNULL(FT_TBL.[StateProvince],'-')  AS HotelCountyState,
    ISNULL(FT_TBL.[PostalCode],'-')     AS HotelPostZipCode,
    ISNULL(FT_TBL.[Latitude],0.00)      AS HotelLatitude,
    ISNULL(FT_TBL.[Longitude],0.00)     AS HotelLongitude,
    ISNULL(FT_TBL.[CheckInTime],'-')    AS HotelCheckinTime,
    ISNULL(FT_TBL.[CheckOutTime],'-')   AS HotelCheckOutTime,
    ISNULL(b.[CountryName],'-')         AS HotelCountry,
    ISNULL(c.PropertyDescription,'-')   AS HotelDescription,
    KEY_TBL.RANK 

    FROM [EAN].[dbo].[tblactivepropertylist] AS FT_TBL INNER JOIN
     CONTAINSTABLE ([EAN].[dbo].[tblEanFullTextSearch], FullTextSearchColumn, @s)
      AS KEY_TBL
    ON FT_TBL.EANHotelID = KEY_TBL.[KEY]
    INNER JOIN [EAN].[dbo].[tblCountrylist] b
    ON FT_TBL.Country = b.CountryCode
    INNER JOIN [EAN].[dbo].[tblPropertyDescriptionList] c
    ON FT_TBL.[EANHotelID] = c.EANHotelID

В коде выше [EAN]. [dbo]. [tblEanFullTextSearch], FullTextSearchColumn - это новая таблица и столбец с добавленными полями, теперь вы можете сделать запрос в новой таблице с присоединениями к таблице, которую вы хотите отобразить данные из.

Надеюсь, что это поможет