无需重新排序即可返回全文搜索结果页面？

发布于 2024-11-14 17:46:48 字数 1041 浏览 0 评论 0原文

我有一个类似于以下内容的查询，用于返回与全文搜索匹配的文章。在生产中，文本[FULL TEXT SEARCH CRITERIA] 被替换为实际的全文搜索表达式。

编写的查询仅返回一页结果。

WITH ArtTemp AS (
  SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY ArtViews DESC) AS RowID,
  Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
  Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
  [User].UsrDisplayName AS UserName FROM Article
  INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
  INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
  INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
  WHERE [FULL TEXT SEARCH CRITERIA] AND Article.ArtApproved = 1
)

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID

除了全文搜索按相关性顺序返回结果之外，此方法工作正常，但 ROW_NUMBER() OVER (ORDER BY ArtViews DESC) 对结果进行排序。有没有办法编写相同的查询而不使用结果？

原文

I have a query similar to the following to return articles that match a full-text search. In production, the text [FULL TEXT SEARCH CRITERIA] is replaced with the actual full-text search expression.

The query is written to return only one page of results.

WITH ArtTemp AS (
  SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY ArtViews DESC) AS RowID,
  Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
  Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
  [User].UsrDisplayName AS UserName FROM Article
  INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
  INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
  INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
  WHERE [FULL TEXT SEARCH CRITERIA] AND Article.ArtApproved = 1
)

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID

This works fine except that full-text search returns the results in order of relevance, but ROW_NUMBER() OVER (ORDER BY ArtViews DESC) resorts the results. Is there any way to write the same query without resorting the results?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瑾兮 2024-11-21 17:46:48

看起来正确的答案是使用 CONTAINSTABLE，它的工作方式与 CONTAINS 类似，但返回一个表而不是布尔 WHERE 条件。

所以我最终得到了这样的东西。

WITH ArtTemp AS (
  SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY FTS.RANK DESC) AS RowID,
  Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
  Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
  [User].UsrDisplayName AS UserName FROM Article
  INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
  INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
  INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
  INNER JOIN CONTAINSTABLE(Article, *, 'FORMSOF(INFLECTIONAL, abc)') AS FTS ON Article.ArtID = FTS.[KEY]
  WHERE Article.ArtApproved = 1
)

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID

It looks like the correct answer to this is to use CONTAINSTABLE, which works similar to CONTAINS but returns a table instead of a Boolean WHERE condition.

So I end up with something like this instead.

WITH ArtTemp AS (
  SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY FTS.RANK DESC) AS RowID,
  Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
  Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
  [User].UsrDisplayName AS UserName FROM Article
  INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
  INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
  INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
  INNER JOIN CONTAINSTABLE(Article, *, 'FORMSOF(INFLECTIONAL, abc)') AS FTS ON Article.ArtID = FTS.[KEY]
  WHERE Article.ArtApproved = 1
)

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID

回复收藏 0 原文

~没有更多了~