SQL、Linq相关字段搜索

结合一些分词组件，如盘古，对于用户查询关键字红按钮很容易分出 ‘红’ ‘按钮’二个单词

我们假设产品名称列里面是红色，规格里面是按钮

/*

普通sql实现全文搜索
declare @key1 nvarchar(50)
declare @key2 nvarchar(50)
declare @key3 nvarchar(50)
set @key1 = '%红%'
set @key2 = '%按钮%'

SELECT *
FROM [SYS_CHANPIN]
where 1=1
and [CPBM] = any(select [CPBM] from [SYS_CHANPIN] where cpmc like @key1 or cpgg like @key1 )
and [CPBM] = any(select [CPBM] from [SYS_CHANPIN] where cpmc like @key2 or cpgg like @key2 )
...................
and [CPBM] = any(select [CPBM] from [SYS_CHANPIN] where cpmc like @keyN or cpgg like @keyN )
*/

 

如果是linq就更简单了

string[] arraykeys = new string[]{"红", "按钮"}

from p in db.SYS_CHANPIN where arraykeys.all(key=>new string[]{p.CPMC,P.CPGG}.Any(column =>column.Contains(key))) select p

 

简单的来说，原理就是对于分组出来的每个单词，都必须在所查询列中满足至少一条,当所有的分词都满足条件时，既为这个数据为我们查询所需要的。