实现数据库驱动的高级搜索引擎以搜索多个表中的数据的简单方法

引言

当我们想要搜索数据库中许多相关表的数据时，我们会创建一个视图来搜索所有相关表的数据，并在该视图上应用搜索字符串，以获得搜索词匹配表名、索引字段及其值的总和作为结果。

背景

要从数据库的多个相关表中获取信息，我们需要对每个表进行查询以获取记录。如果我们不知道哪个表或关系包含搜索数据，并且我们希望搜索能够找到表、关系以及数据库中的记录，我们将使用此方法。

使用代码

我们将创建一个视图来保存所有相关表和搜索数据。

视图 - SearchData

SELECT LastName + FirstName + NickName AS SearchText,
PersonId AS Id, 'Person' AS TableName, 'PersonId ' AS IdField
FROM dbo.Person
UNION
SELECT FirstName + LastName AS SearchText, DOCTORId AS Id, 'DOCTOR' AS TableName, 'DOCTORId' AS IdField
FROM dbo.DOCTOR

SearchText：包含用于搜索数据的表字段

Id：包含索引字段的值

TableName：包含表名

IdField：包含使用 Id 值查找相关表记录的字段名

从搜索字符串创建表的函数

function [dbo].[String_Split](@String nvarchar (4000),@Delimiter nvarchar (10)) returns ValueTable table ([Value] nvarchar(4000))

begin

declare @NextString nvarchar(4000)

declare @Pos int

declare @NextPos int

declare @CommaCheck nvarchar(1)

set @NextString = ''

set @CommaCheck = right(@String,1)

set @String = @String + @Delimiter

set @Pos = charindex(@Delimiter,@String)

set @NextPos = 1

while (@pos <> 0)

begin

set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos

set @pos = charindex(@Delimiter,@String)

end

return

end

获取搜索数据

PROCEDURE [dbo].[AdvanceSearch] @searchtring varchar(1000)
AS
BEGIN

--declare @searchtring varchar(1000)
--set @searchtring = 'wi,jo'

set @searchtring = replace(@searchtring,',',' ')
set @searchtring = replace(@searchtring,';',' ')
set @searchtring = replace(@searchtring,'.',' ')
--set @searchtring = replace(@searchtring,'.',' ')

--select '%' + value + '%' as Keys from String_Split( @searchtring ,' ')

SELECT count(Id) as MatchFildsCount , SearchText, Id, TableName, IdField
FROM SearchData join
(select '%' + value + '%' as Keys from String_Split( @searchtring ,' ')) searchKeyTable
on SearchText like Keys
group by SearchText, Id, TableName, IdField
order by count(Id)desc

END

例如：

AdvanceSearch N'wi,jo,sir'

匹配字段数	搜索文本	ID	TableName	ID字段
2	BradmanJohnSir	3	人员	PersonId
2	WilliamsJohn	5	人员	PersonId
1	WinstonChurchillWinky	45	人员	PersonId
1	CookJoanJoan	34	人员	PersonId
1	JohnriedMan	3	DOCTOR	DOCTORId
1	JonesDeanJon	35	人员	PersonId
1	JonesMariaMaria	43	人员	PersonId
1	LeoWilliams	28	DOCTOR	DOCTORId
1	LeoWilliams	30	DOCTOR	DOCTORId
1	LeoWilliams	31	DOCTOR	DOCTORId
1	LeoWilliams	32	DOCTOR	DOCTORId
1	TaylorJonesjones	30	人员	PersonId
1	WilliamsHarryHarry	59	人员	PersonId