web开发之搜索、排序

一时失言乱红尘 2022-09-18 02:00 130阅读 0赞

##  功能概述 ##

web开发中，查询搜索时，常见需要根据一定字段进行排序后取得的查询结果，有时排序规则复杂时，简单的sql语句不能实现，需要用搜索引擎或排名的组件才能实现功能。

### 简单sql语句匹配搜索、排序 ###

如:取得订单列表，实现订单状态指定为“待处理”的订单排名靠前，我们用sql语句实现如下：

select 1 sort,a.\* from order a where a.orderstatus='initial' union select 2 sor,a.\* from order a where a.orderstatus!='initial' order by asc;

### [SQL匹配多字段进行排序，搜索][SQL]（[http://blog.csdn.net/diligentcatrich/article/details/6766902][SQL]） ###

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

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

<table> 
 <tbody> 
 <tr> 
 <td>字段</td> 
 <td>类型</td> 
 </tr> 
 <tr> 
 <td>ProdID</td> 
 <td>int</td> 
 </tr> 
 <tr> 
 <td>ProdName</td> 
 <td>nvarchar</td> 
 </tr> 
 <tr> 
 <td>CategoryName</td> 
 <td>nvarchar</td> 
 </tr> 
 <tr> 
 <td>ProdBrand</td> 
 <td>nvarchar</td> 
 </tr> 
 <tr> 
 <td>ProdIntro</td> 
 <td>nvarchar</td> 
 </tr> 
 <tr> 
 <td>ProdDescription</td> 
 <td>nvarchar</td> 
 </tr> 
 </tbody> 
</table>

现在我们要求通过某个关键字从Products表中搜索包含该关键字的记录，凡是以下任何一个字段包含该关键字的记录都列出来：ProdName, CategoryName , ProdBrand, ProdIntro, ProdDescription。 并且搜索结果按照前述字段的匹配优先级进行排序：

1）先列出字段ProdName匹配关键字的记录，然后列出字段CategoryName匹配关键字的记录，依此类推，最后列出字段ProdDescription匹配关键字的记录；

2）在字段ProdName匹配关键字的所有记录中，先列出字段CategoryName也匹配关键字的记录，然后列出字段ProdBrand也匹配关键字的记录，依次类推…

3）按照规则2递归排序每个记录分组……

搜索匹配该关键字的所有记录的SQL语句倒很简单：

1.  SELECT \*  
2.  from Products  
3.  WHERE ProdName like '%KeyWord%' or CategoryName like '%KeyWord%' or ProdBrand like '%KeyWord%' or ProdIntro like '%KeyWord%' or ProdDescription like '%KeyWord%'

但对搜索出的结果进行匹配优先级排序稍微有点困难。在用简单的SQL进行搜索时有两种方式来达到这个排序的目的：**加权法**和**多字段排序法**（我瞎取的名字^-^）。

**一、加权法**

对搜索的每条记录计算出一个**排序权值**来，然后将所有搜索结果按照这个**排序权值**进行降序排列即可。每条被搜索出的记录的排序权值为该记录所有字段的**权值**之和。某个字段的**权值**取决于该字段是否匹配关键字，如果不匹配则为**０**，如果匹配则为改字段的**匹配权值**。字段的**匹配权值**计算方式为：

*fieldPriority = 2的ｉ次冥*（i为该字段在所有被搜索的字段优先级排序中倒排的位置）

例如，在我们示例中各字段的匹配权值为：

<table> 
 <tbody> 
 <tr> 
 <td> 字段 </td> 
 <td> 倒排位置 </td> 
 <td> 匹配权值 </td> 
 </tr> 
 <tr> 
 <td> ProdName </td> 
 <td> ４ </td> 
 <td> 16 </td> 
 </tr> 
 <tr> 
 <td> CategoryName </td> 
 <td> ３ </td> 
 <td> 8 </td> 
 </tr> 
 <tr> 
 <td> ProdBrand </td> 
 <td> ２ </td> 
 <td> 4 </td> 
 </tr> 
 <tr> 
 <td> ProdIntro </td> 
 <td> １ </td> 
 <td> 2 </td> 
 </tr> 
 <tr> 
 <td> ProdDescription </td> 
 <td> ０ </td> 
 <td> 1 </td> 
 </tr> 
 </tbody> 
</table>

之所以采用这种算法，是为了确保某个字段匹配的记录的**排序权值**不会低于另外一条**不匹配该字段**但**后续字段都匹配**的记录的**排序权值**。例如记录Ａ中**仅仅ProdName匹配关键字**，所以它的**排序权值**为16，而记录B中**除了字段ProdName外其他字段都匹配**，则其**排序权值**为15（8+4+2+1=15）。但记录A仍然会排在记录B前面。

相应的SQL大致如下：

1.  SELECT \*,　(  
2.                       (case when charIndex(ProdName,KeyWord)>-1 then 16 else 0 end) +  
3.                       (case when charIndex(CategoryName,KeyWord)>-1 then 8 else 0 end) +  
4.                       (case when charIndex(ProdBrand,KeyWord)>-1 then 4 else 0 end) +  
5.                       (case when charIndex(ProdIntro,KeyWord)>-1 then 2 else 0 end) +  
6.                       (case when charIndex(ProdDescription,KeyWord)>-1 then 1 else 0 end)  
7.                    )  as OrderPriority  
8.  from Products  
9.  WHERE ProdName like ‘%KeyWord%'　or 
10.             CategoryName like ‘%KeyWord%'　or  
11.             ProdBrand like ‘%KeyWord%'　or 
12.             ProdIntro like ‘%KeyWord%'　or  
13.             ProdDescription like ‘%KeyWord%'  
14. Order by OrderPriority desc

**二、多字段排序法**

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

1.  SELECT \*  
2.  from Products  
3.  WHERE ProdName like ‘%KeyWord%'　or 
4.              CategoryName like ‘%KeyWord%'　or  
5.              ProdBrand like ‘%KeyWord%'　or 
6.              ProdIntro like ‘%KeyWord%'　or  
7.              ProdDescription like ‘%KeyWord%'  
8.  Order by        (case when charIndex(ProdName,KeyWord)>-1 then 0 else 1 end),  
9.                       (case when charIndex(CategoryName,KeyWord)>-1 then 0 else 1 end),  
10.                      (case when charIndex(ProdBrand,KeyWord)>-1 then 0 else 1 end),  
11.                      (case when charIndex(ProdIntro,KeyWord)>-1 then 0 else 1 end),  
12.                      (case when charIndex(ProdDescription,KeyWord)>-1 then 0 else 1 end)

[SQL]: http://blog.csdn.net/diligentcatrich/article/details/6766902