操纵排名的关键是使用联合体。对于每一列,您使用单独的 select 语句。在该语句中,添加一个标识符,显示每行是从哪一列中提取的。将结果插入表变量中,然后您可以通过对标识符进行排序或将排名乘以基于标识符的某个值来操纵排名。
关键是给出修改排名的外观,而不是实际改变sql server的排名。
使用表变量的示例:
DECLARE @Results TABLE (PersonId Int, Rank Int, Source Int)
对于有列的表 PeoplePersonId Int PK Identity, FirstName VarChar(100), MiddleName VarChar(100), LastName VarChar(100), AlsoKnown VarChar(100)
将每一列添加到全文目录后,您可以使用以下查询:
INSERT INTO @Results (PersonId, Rank, Source)
SELECT PersonId, Rank, 1
FROM ContainsTable(People, FirstName, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId
UNION
SELECT PersonId, Rank, 2
FROM ContainsTable(People, MiddleName, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId
UNION
SELECT PersonId, Rank, 3
FROM ContainsTable(People, LastName, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId
UNION
SELECT PersonId, Rank, 4
FROM ContainsTable(People, AlsoKnown, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId
/*
Now that the results from above are in the @Results table, you can manipulate the
rankings in one of several ways, the simplest is to pull the results ordered first by Source then by Rank. Of course you would probably join to the People table to pull the name fields.
*/
SELECT PersonId
FROM @Results
ORDER BY Source, Rank DESC
/*
A more complex manipulation would use a statement to multiply the ranking
by a value above 1 (to increase rank) or less than 1 (to lower rank), then
return results based on the new rank. This provides more fine tuning,
since I could make first name 10% higher and middle name 15% lower and
leave last name and also known the original value.
*/
SELECT PersonId, CASE Source WHEN 1 THEN Rank * 1.1 WHEN 2 THEN Rank * .9 ELSE Rank END AS NewRank FROM @Results
ORDER BY NewRank DESC
一个缺点是你会注意到我没有使用UNION ALL
,因此如果一个单词出现在多个列中,则排名不会反映这一点。如果这是你可以使用的问题UNION ALL
然后通过将全部或部分重复记录排名添加到具有相同人员 ID 的另一个记录的排名来删除重复的人员 ID。