SQL Server 2008 -全文搜索与多个关键字和分页

本文关键字：关键字 分页 搜索 2008 Server 文搜索 SQL | 更新日期: 2023-09-27 18:11:32

我不是一个数据库人(正如这个问题所示)。我试图写一个表值函数，我可以使用Linq Sql在我的ASP。. NET web应用程序

假设我想搜索标题或摘要可能包含多个关键词的书籍(例如"澳大利亚历史")。

我想一次显示x个结果，所以我需要能够告诉我的函数"跳过"answers"取"一定数量的记录。

有些书可能被标记为"已删除"。我不想退货。

到目前为止我写的是:

CREATE FUNCTION SearchBooks
(
    @keywords nvarchar(50),
    @skip int,
    @take int
)
RETURNS TABLE
AS
RETURN
(
  SELECT TOP (@take)
         ROW_NUMBER() OVER (ORDER BY MyFTS.RANK DESC) AS RowID,
         MyFTS.RANK as Relevance,
         [ID],
         [Title],
         [Summary]
    FROM [Book]
    JOIN CONTAINSTABLE ([Book], ([Title], [Summary]), @keywords, @take) AS MyFTS ON MyFTS.[KEY] = [Book].[ID]
   WHERE ([Book].[Deleted] = 0) 
     AND (MyFTS.[RowID] BETWEEN (@skip + 1) AND (@skip + @take))
ORDER BY MyFTS.RANK DESC
)

当我尝试创建此函数SSMS给我错误"无效列名'RowID'"。

我想我还需要获得匹配记录的总数，以便我可以设置分页?

我希望你们中的哪位SQL大师能给我指路。

SQL Server 2008 -全文搜索与多个关键字和分页

不能在WHERE子句中引用列别名-使用内联视图/派生表:

  SELECT x.rowid,
         x.relevance,
         x.id,
         x.title,
         x.summary
   FROM (SELECT TOP (@take)
                ROW_NUMBER() OVER (ORDER BY MyFTS.RANK DESC) AS RowID,
                MyFTS.RANK as Relevance,
                [ID],
                [Title],
                [Summary]
           FROM [Book] b
           JOIN CONTAINSTABLE ([Book], ([Title], [Summary]), @keywords, @take) AS MyFTS ON MyFTS.[KEY] = b.id
          WHERE b.[Deleted] = 0) AS x
    WHERE x.[RowID] BETWEEN (@skip + 1) AND (@skip + @take))
ORDER BY x.relevance DESC

谢谢OMG小马-这正是我所追求的。我已经修改了这个函数，使它也返回匹配行的总数(TotalRows)，现在我应该能够让分页工作。

CREATE FUNCTION SearchBooks
(
    @keywords nvarchar(50),
    @skip int,
    @take int
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@take) * FROM 
    (
        SELECT TOP (@take)
            ROW_NUMBER() OVER (ORDER BY MyFTS.RANK DESC) AS RowID,
            COUNT(*) OVER(PARTITION BY 1) as TotalRows,
            MyFTS.RANK as Relevance,
            [ID],
            [Title],
            [Summary]
        FROM [Book] b
        JOIN CONTAINSTABLE ([Book], ([Title], [Summary]), @keywords, @take) AS MyFTS ON MyFTS.[KEY] = b.[ID]
        WHERE b.[Deleted] = 0
    ) AS x
    WHERE x.[RowID] BETWEEN (@skip + 1) AND (@skip + @take)
    ORDER BY x.relevance DESC
)
相关文章：