SQL 算法问题 - for 循环添加和过滤结果 [英] SQL Algorithm Trouble - for loop to add and filter results

问题描述

我的搜索功能再次为 SQL 苦苦挣扎.

I am struggling yet again with SQL with my search function.

基本上，我想要一个存储过程，将字符串拆分为单词，然后对于每个单词，我想使用全文搜索检查它是否在 2 个表中的任何一个中.

Basically I want a stored proc that splits up a string into words then for each word I want to check using Full Text Search if it is in any of 2 tables.

如果在这些表中找到结果，它应该将它们添加到它为其他词找到的任何结果中，并返回一个记录在这两个集合中的集合.

If results are found in these tables it should add them to any results it found for the other words and return a set where the record is in both these sets.

这是我的算法

if null return all restaurants

declare results = empty

for each word
if in restaurant.name or cuisine.name
    addRowsToResults 
else if in restaurant.city
    addRowsToResults 
else if in restaurant.postcode
    addRowsToResults 


addRowsToResults 
results = (results + new results) where a match is in both

我不知道从哪里开始，我已经在谷歌上搜索了很长时间，但作为 SQL 初学者，我可能会遗漏一些术语.

I have no idea where to start with this and I have searched google for ages but being a begginer in SQL I may be missing some terms.

这也是做这种事情的最好方法吗?

Also is this even the best way to do this sort of thing?

感谢任何帮助.

只是为了提供更多信息.城市、邮政编码和名称都是餐厅表中的 nvarchar 字段.菜名在另一张桌子上，并通过另一张桌子链接到餐厅，因为餐厅可以提供多种类型的美食.

Just to give more info. City, Postcode and Name are all nvarchar fields in a Restaurants table. Cuisine name is in a different table and is linked through another table to restaurants as a restaurant can serve many types of cuisine.

所有这些字段都有全文搜索索引.

All these fields have full text search indexing.

推荐答案

这里是一个分割函数，它有一个分隔符的可选参数...

Here is a split function that has an optional parameter of a delimiter...

CREATE FUNCTION [dbo].[fnSplit]( @List VARCHAR(4000), @Delimiter CHAR(1) = ',' )
    RETURNS @Result TABLE (item VARCHAR(100))
    BEGIN
        DECLARE @Item VARCHAR(100)
        WHILE CHARINDEX(@Delimiter,@List,0) <> 0
        BEGIN
            SELECT @Item = SUBSTRING(@List,1,CHARINDEX(@Delimiter,@List,0)-1)
                , @List = SUBSTRING(@List,CHARINDEX(@Delimiter,@List,0)+1,LEN(@List))

            IF LEN(@Item) > 0
                INSERT INTO @Result
                    SELECT @Item
        END
        IF LEN(@List) > 0
            INSERT INTO @Result
                SELECT @List
        RETURN
    END
GO

那么您的查询可能看起来像这样...

Then your query could look something like this...

SELECT DISTINCT
      'restaurants' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name + '(' + r.city + ', ' + r.state + ')' AS [Description]
FROM restaurants AS [r]
JOIN [dbo].[fnSplit](@Query,' ') AS [terms]
ON
    ISNULL(r.name,'')
    +','+ISNULL(r.city,'')
    +','+ISNULL(r.postcode,'') LIKE '%'+terms.item+'%'

UNION SELECT DISTINCT
      'cuisine' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name AS [Description]
FROM cuisine AS [r]
JOIN [dbo].[fnSplit](@Query,' ') AS [terms]
ON
    ISNULL(r.name,'') LIKE '%'+terms.item+'%'

fnSplit 函数将您的查询词分解为表变量中的行并返回它.然后选择查询连接到结果表.查询是不同的，因此无论查询中匹配了多少项，都只返回一行的 1 个实例.连接条件可以很容易地分解为一系列 AND/OR 条件，但我认为 LIKE 操作比连接更昂贵，所以我只是连接列.

The fnSplit function breaks your query terms into rows in a table variable and returns it. The select queries then joins against the resultant table. The query is distinct so that only 1 instance of a row is returned regardless of how many terms are matched in the query. The join condition could easily be broken into a series of AND/OR conditions but I think LIKE operations are more expensive than concatenation so I'm just concatenating the columns.

更新

由于启用了全文索引，可以使用以下简化查询.

The following simplified query can be used since Full-Text Indexing is enabled.

DECLARE @Phrase VARCHAR(4000)
SELECT @Phrase = item + '" OR "' FROM [dbo].[fnSplit](@Query,' ')
SET @Phrase = SUBSTRING(@Phrase,0,LEN(@Phrase)-6)


SELECT DISTINCT
      'restaurants' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name + '(' + r.city + ', ' + r.state + ')' AS [Description]
FROM restaurants AS [r]
WHERE
    CONTAINS(r.name,@Phrase)
    OR CONTAINS(r.city,@Phrase)
    OR CONTAINS(r.postcode,@Phrase)

UNION SELECT DISTINCT
      'cuisine' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name AS [Description]
FROM cuisine AS [r]
WHERE CONTAINS(r.name,@Phrase)

这篇关于SQL 算法问题 - for 循环添加和过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！