使用WITH TempResults AS时返回两个结果集 [英] Return two result sets when using WITH TempResults AS

问题描述

我有以下查询，我想返回两个结果集。一个是表格的结果，另一个是返回潜在结果的数量，或者是MaxResults。

I have the following query where I would like to return two result sets. One being the results as a table and the second just returning the number of potential results, or MaxResults.

最后一行错误无效的对象名称ResultsTemp 。直到我注释掉第二行，它才会起作用。看来我只能使用ResultsTemp一次。

The last line errors with Invalid object name ResultsTemp. It does not work until I comment out the second last line. It appears I can only use the ResultsTemp once.

DECLARE @StartRow int;
DECLARE @MaxRows int;
set @StartRow = 0;
set @MaxRows = 5;

WITH ResultsTemp AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY FTS.RANK DESC) AS RowId,
    Id, Name FROM tNews
    INNER JOIN CONTAINSTABLE(tNews, *, 'FORMSOF(INFLECTIONAL, hello)') 
        AS FTS ON tNews.Id = FTS.[KEY]
)

SELECT Id, Name, RowId FROM ResultsTemp 
Group By Id, Name, RowId 
Having RowId between @StartRow and (@StartRow + @MaxRows);

select COUNT(*) from ResultsTemp;

谢谢

thank you

推荐答案

Create table #temp
(
    Id int, 
    Name Varchar(100), 
    RowId int
)

DECLARE @StartRow int;
DECLARE @MaxRows int;
set @StartRow = 0;
set @MaxRows = 5;

SELECT ROW_NUMBER() OVER (ORDER BY FTS.RANK DESC) AS RowId,
Id, Name into #temp FROM tNews
INNER JOIN CONTAINSTABLE(tNews, *, 'FORMSOF(INFLECTIONAL, hello)') 
AS FTS ON tNews.Id = FTS.[KEY]

SELECT Id, Name, RowId FROM #temp
Group By Id, Name, RowId 
Having RowId between @StartRow and (@StartRow + @MaxRows);

select COUNT(RowId) from #temp;

这篇关于使用WITH TempResults AS时返回两个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！