搜索结果按匹配字段进行排序

时间：2017-03-09 09:04:48

搜索结果按匹配字段进行排序
    谈到搜索，一般就想起了SQL Server的FullTextSearch（全文搜索）功能，它确实强大，但使用起来也要做一些较为繁琐的准备工作，一般小型的项目或者对搜索要求（包括性能需求）不是很高的情况下实用它还是有点太重型了。简单的搜索用SQL查询即可，但一般面临的一个问题就是如何对搜索结果按匹配字段进行优先级排序。

    例如有个产品表(Products)，它的字段包：括产品ID、产品名称、产品类别、产品品牌、产品简介、产品详细介绍。

字段	类型
ProdID	int
ProdName	nvarchar
CategoryName	nvarchar
ProdBrand	nvarchar
ProdIntro	nvarchar
ProdDescription	nvarchar

现在我们要求通过某个关键字从Products表中搜索包含该关键字的记录，凡是以下任何一个字段包含该关键字的记录都列出来：ProdName, CategoryName , ProdBrand, ProdIntro, ProdDescription。 并且搜索结果按照前述字段的匹配优先级进行排序：
  1）先列出字段ProdName匹配关键字的记录，然后列出字段CategoryName匹配关键字的记录，依此类推，最后列出字段ProdDescription匹配关键字的记录；
  2）在字段ProdName匹配关键字的所有记录中，先列出字段CategoryName也匹配关键字的记录，然后列出字段ProdBrand也匹配关键字的记录，依次类推…
  3）按照规则2递归排序每个记录分组……
  搜索匹配该关键字的所有记录的SQL语句倒很简单：
SELECT *　from Products WHERE ProdName like ‘%KeyWord%'　or CategoryName like ‘%KeyWord%'　or ProdBrand like ‘%KeyWord%'　or ProdIntro like ‘%KeyWord%'　or ProdDescription like ‘%KeyWord%'
 但对搜索出的结果进行匹配优先级排序稍微有点困难。在用简单的SQL进行搜索时有两种方式来达到这个排序的目的：加权法和多字段排序法（我瞎取的名字^-^）。
一、加权法
　对搜索的每条记录计算出一个排序权值来，然后将所有搜索结果按照这个排序权值进行降序排列即可。每条被搜索出的记录的排序权值为该记录所有字段的权值之和。某个字段的权值取决于该字段是否匹配关键字，如果不匹配则为０，如果匹配则为改字段的匹配权值。字段的匹配权值计算方式为：
　　　fieldPriority = 2的ｉ次冥（i为该字段在所有被搜索的字段优先级排序中倒排的位置）
　例如，在我们示例中各字段的匹配权值为：

字段	倒排位置	匹配权值
ProdName	４	16
CategoryName	３	8
ProdBrand	２	4
ProdIntro	１	2
ProdDescription	０	1

之所以采用这种算法，是为了确保某个字段匹配的记录的排序权值不会低于另外一条不匹配该字段但后续字段都匹配的记录的排序权值。例如记录Ａ中仅仅ProdName匹配关键字，所以它的排序权值为16，而记录B中除了字段ProdName外其他字段都匹配，则其排序权值为15（8+4+2+1=15）。但记录A仍然会排在记录B前面。
   SQL Server 相应的SQL大致如下：[注mysql中要把下面的charIndex()换成INSTR()]

SELECT *,　(
                     (case when charIndex(ProdName,KeyWord)>-1 then 16 else 0 end) +
                     (case when charIndex(CategoryName,KeyWord)>-1 then 8 else 0 end) +
                     (case when charIndex(ProdBrand,KeyWord)>-1 then 4 else 0 end) +
                     (case when charIndex(ProdIntro,KeyWord)>-1 then 2 else 0 end) +
                     (case when charIndex(ProdDescription,KeyWord)>-1 then 1 else 0 end)
                  )  as OrderPriority
from Products 
WHERE ProdName like ‘%KeyWord%'　or 
            CategoryName like ‘%KeyWord%'　or 
            ProdBrand like ‘%KeyWord%'　or 
            ProdIntro like ‘%KeyWord%'　or 
            ProdDescription like ‘%KeyWord%'
Order by OrderPriority desc

二、多字段排序法
     加权法实在是有点啰嗦，倒不如直接利用SQL可以对多个字段进行排序来实现更清晰更直接。实际上我们把每个字段是否匹配的权值分散到SQL的Order里即可，大致SQL如下：
 

SELECT * 
from Products 
WHERE ProdName like ‘%KeyWord%'　or 
            CategoryName like ‘%KeyWord%'　or 
            ProdBrand like ‘%KeyWord%'　or 
            ProdIntro like ‘%KeyWord%'　or 
            ProdDescription like ‘%KeyWord%'
Order by        (case when charIndex(ProdName,KeyWord)>-1 then 0 else 1 end),
                     (case when charIndex(CategoryName,KeyWord)>-1 then 0 else 1 end),
                     (case when charIndex(ProdBrand,KeyWord)>-1 then 0 else 1 end),
                     (case when charIndex(ProdIntro,KeyWord)>-1 then 0 else 1 end),
                     (case when charIndex(ProdDescription,KeyWord)>-1 then 0 else 1 end)