Как запросить базу данных SQL Server 2008 для имени и фамилии и порядка уместностью?
В основном у меня есть таблица как это:
CREATE TABLE Person(
PersonID int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(512) NOT NULL,
LastName nvarchar(512) NULL
)
И я должен найти вершину n результатами на основе пользовательского запроса как это:
"Joh Smi"
Следующий запрос возвращает результаты, в которых я нуждаюсь (я думаю). Просто не в соответствующем порядке.
SELECT
PersonID, FirstName, LastName
FROM
Person
WHERE
FirstName LIKE 'Joh%' OR
LastName LIKE 'Joh%' OR
FirstName LIKE 'Smi%' OR
LastName LIKE 'Smi%'
Если следующие имена были в базе данных, и нашим пользовательским запросом был "Joh Smi", имена должны появиться в следующем порядке (или подобный)
- John Smith
- Johnny Smith
- John Jacob
- Смитсоновский институт David
- Daniel Johnson
Я надеюсь заставить это работать подобное поиску друга автоматического заполнения Facebook.
Так, как я возвращаю вершину n большинство соответствующих строк в SQL Server 2008?
3 ответа
В качестве дополнения к ответу OMG Ponies ...
Чтобы получить наилучшие результаты от полнотекстового поиска, вы можете создать индексированное представление, объединяющее поля имени и фамилии.
Это позволит вам более точно взвесить отдельные части полного имени.
Пример кода выглядит следующим образом:
CREATE VIEW [dbo].[vFullname] WITH SCHEMABINDING AS
SELECT personID, FirstName + ' ' + LastName AS name
FROM dbo.person
WITH ranks AS(
SELECT FT_TBL.personid
,FT_TBL.name
,KEY_TBL.RANK
FROM dbo.vfullname AS FT_TBL
INNER JOIN CONTAINSTABLE(vfullname, (name),
'ISABOUT ("Smith" WEIGHT (0.4), "Smi*" WEIGHT (0.2),
"John" WEIGHT (0.3), "Joh*" WEIGHT (0.1))') AS KEY_TBL
ON FT_TBL.personid = KEY_TBL.[KEY]
)
SELECT
r.personID,
p.firstname,
p.lastname,
r.rank
FROM ranks r INNER JOIN
person p ON r.personID = p.personID
ORDER BY rank DESC;
CTE просто позволяет вам возвращать отдельные поля имени и фамилии. Если они вам не нужны, проигнорируйте их.
Я рекомендую реализовать полнотекстовый поиск (FTS) по двум причинам:
- Упростить запрос (не нужны операторы OR, которые будут плохо работать. )
- Используйте функцию ранжирования FTS - см. эту статью
Сначала вам нужно решить, что вы подразумеваете под релевантностью. Вот мой список релевантности
- Оба слова - это точно имя и фамилия
- То же, что и первое, но в обратном порядке
- Одно - полное слово, другое - подстрока
- То же, что и третье
- Оба - подстроки. Внутри этого можно отсортировать по длине подстроки (больше - значит хорошо)
- Один - подстрока
Итак, я бы рекомендовал вам создать функцию, которая будет принимать имя, фамилию и входную строку и возвращать int или float в соответствии с правилами, а затем сортировать по ним.