模糊查询——搜索结果按字段的权重排序

项目需要做一个搜索功能，要求文章标题优先其次是标签然后是描述（就是标题里含有搜索词的先显示然后是标签里含有的最后是描述里含有的），自己思考后没有具体的实现思路就在网上查了一下查到如下一种模糊搜索的方法感觉挺简洁也挺好用的。

问题：如何对搜索结果按匹配字段进行优先级排序。

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

 CREATE TABLE `products` (
  `ProdID` int(4) NOT NULL default '0' COMMENT '产品ID',
  `ProdName` varchar(50) default NULL COMMENT '产品名称',
  `CategoryName` varchar(50) default NULL COMMENT '产品类别',
  `ProdBrand` varchar(50) default NULL COMMENT '产品品牌',
  `ProdIntro` varchar(50) default NULL COMMENT '产品简介',
  `ProdDescription` varchar(50) default NULL COMMENT '产品详细介绍',
  PRIMARY KEY  (`ProdID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='产品表';

现在我们要求通过某个关键字从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大致如下：

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

注；如果是mysql数据库可以将charIndex替换为instr结果一样。

二、多字段排序法

加权法实在是有点啰嗦，倒不如直接利用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)

注；如果是mysql数据库可以将charIndex替换为instr结果一样。

顺便贴一段在MyBatis里采用这种思路来实现搜索的代码：

<select id="findByNameCg" resultType="themeSearch">
		SELECT
			id theme_id,
			`name`,
			description,
			picture,
			default_attention state,
           (
           (case when instr(name,#{name})> 0 then 16 else 0 end) +
           (case when instr(id,'')> 0 then 8 else 0 end) +
           (case when instr(description,#{description})> 0 then -1 else 0 end)
        )  as OrderPriority
		FROM
			cc_group
		WHERE
			offline = 1
		AND `name` LIKE concat('%',#{name},'%')
		OR description LIKE concat('%',#{description},'%')
		OR id IN (
			SELECT
				ctt.theme_id
			FROM
				cc_tags ct
			JOIN cc_theme_tag ctt ON ct.id = ctt.tag_id
			WHERE
				ct.`name` LIKE concat('%',#{name},'%')
		)
		ORDER BY OrderPriority DESC,content_num DESC
		LIMIT #{pageNo},
				#{pageSize}
	</select>

本人感觉自己写的这个sql并不是很满意如果那个大神有好的建议请留言告知，灰常感谢。