搜索功能，SQL Server [英] Search function, SQL Server

问题描述

我正在我的网站上创建一个小的搜索功能，这使得可以在系统中搜索文章.每篇文章都有一组与之关联的关键字，这些关键字存储在SQL Server数据库中.

I am creating a small search function on my site, this enables the search for articles in the system. Each article has a set of keywords associated to it and these keywords are stored inside a SQL Server database.

这是表格:

CREATE TABLE [dbo].[SearchWords] (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ArticleID] [int] NOT NULL,
    [SearchWord] [nvarchar](20) NOT NULL,
    CONSTRAINT [PK_SearchWords] PRIMARY KEY CLUSTERED 
        ([ID] ASC) 
        WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS  = ON)
        ON [PRIMARY]
) ON [PRIMARY]

每篇文章可以包含无限数量的关键字.现在我的问题是搜索本身. 例如，当用户键入:

Each article can have an unlimited amount of keywords. Now my problem is the search itself. When for example the user types:

法国演员

我希望系统查找所有包含关键字France and actors的文章(仅一次DISTINCT).我将搜索条件作为varchar(用空格分隔)传递给存储过程.然后，我使用以下函数将单词拆分:(Erland Sommarskog) http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings

I want the system to find all articles (just once DISTINCT) with the keywords France and actors. I am passing the search criteria as a varchar (separated with space) to the stored procedure. Then i am splitting the words up with the following function: (Erland Sommarskog) http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings

然后我如何将标准词与搜索词匹配，并仅获得不同的文章ID?

How do i then match the criteria words against the search words and only get the distinct article ids?

我正在使用类似的方法，只是我不明白如何匹配所有关键字.如果仅输入一个关键字，则此方法有效.如果用户输入多个，则即使文章包含所有涉及的关键字，它也不会返回任何内容.

I am working with something like this, just that i cant understand how to match all the keywords. This method works if just one keyword is entered. If the user enters multiple, then it doesn't return anything even if the article has all involved keywords.

declare @temp nvarchar(50)
set @temp = 'France actors'

SELECT DISTINCT Article.ArticleID 
FROM Article
INNER JOIN SearchWords 
    ON Article.ArticleID = SearchWords.ArticleID
JOIN iter_charlist_to_tbl(@temp, DEFAULT) s 
    ON SearchWords.SearchWord = s.nstr

有什么想法吗?

推荐答案

首先，链接的UDF的默认定界符为,字符，而不是空格.因此，按原样使用默认的定界符，您将返回一行，其中包含两个单词. (调试提示:当某些功能无法正常工作时，将其拆开.在这种情况下，您应该执行select * from UDF(@temp, DEFAULT)来查看表是否正确.)

First off, your linked UDF's default delimiter is the , character, not space. So, using the default delimiter as you are you're getting a single row back with both words in it. (Debugging hint: when something isn't working right, take it apart. In this case, you should have done a select * from UDF(@temp, DEFAULT) to see if the table looked correct.)

假设您要继续使用该UDF，并且想要与任何搜索词(但不一定是所有搜索词)匹配的文章，那么以下几句话应该是正确的:

Assuming you want to keep using that UDF and you want articles that match any of the search terms (but not necessarily all), something along these lines should be correct:

declare @temp nvarchar(50)
set @temp = 'France actors'

SELECT DISTINCT 
  a.ArticleID 
FROM 
  Article a
  JOIN SearchWords sw ON a.ArticleID = sw.ArticleID
WHERE
  exists (
    select 
      1 
    from 
      iter_charlist_to_tbl(@temp, ' ') s 
    where
      s.nstr = sw.SearchWord
  )

如果将参数更改为UDF，则内部联接方法可能也应该起作用.

Your inner join method probably should work as well if you change the parameters to the UDF.

这篇关于搜索功能，SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！