tsql - 如何从 SQL Server CTE 设置参数值？

Question

给定以下 SQL:

;WITH Results AS
    (
        SELECT ItemListID, Title,  
        ROW_NUMBER() OVER(ORDER BY ItemListID DESC) as intRow, 
        COUNT(ItemListID) OVER() AS ActualNumberOfResults 
        FROM ItemData
        WHERE FREETEXT(Title, @FTSSearchTerm)
        AND WebsiteID = @WebsiteID
        AND ContentTypeID = @ContentTypeID
        AND GeoID2 = @GeoID                                 
     )
SELECT * FROM Results
WHERE intRow BETWEEN @intStartRow AND @intEndRow
ORDER BY ItemListID DESC    

SELECT @NumberOfResultsReturned = @@ROWCOUNT

我想将参数 @ActualNumberOfResults 设置为从 CTE 返回的 TOP(1) ActualNumberOfResults 的值。

我在 CTE 之后使用了以下内容，但似乎是重复的:

SELECT @ActualNumberOfResults = COUNT(*)
FROM ItemData
WHERE CONTAINS(Title, @FTSSearchTerm  )
AND WebsiteID=@WebsiteID 

我怎样才能做到这一点？

Answer 1

;WITH cte AS
 (
  SELECT ItemListID, Title,
         CASE WHEN ROW_NUMBER() OVER(ORDER BY ItemListID DESC) 
                   BETWEEN @intStartRow AND @intEndRow THEN 1
                   END AS NumberOfResultsReturned,      
         COUNT(ItemListID) OVER() AS ActualNumberOfResults          
  FROM ItemData
  WHERE FREETEXT(Title, @FTSSearchTerm)
        AND WebsiteID = @WebsiteID
        AND ContentTypeID = @ContentTypeID
        AND GeoID2 = @GeoID                                 
 )
 SELECT @NumberOfResultsReturned = COUNT(NumberOfResultsReturned), 
        @ActualNumberOfResults = MAX(ActualNumberOfResults)
 FROM cte         

演示 SQLFiddle