sql - 如何重用通用表表达式

我正在使用通用表表达式进行分页:

with query as (
  Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
)
Select * from query where TableRowNum between 1 and 25 Order By TableRowNum ASC

进行此查询后，我立即进行几乎相同的查询以检索项目总数:

with query as (
  Select Row_Number() over (Order By OrderNum ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
)
Select Count(*) from query

我尝试将它们组合在一起(即:定义CTE，查询数据，然后查询Count，但是当我这样做时，响应第二个查询(Count)，我收到一条错误消息“Invalid object name'query'” )。

有什么方法可以将这两个查询合并为一个，以节省往返数据库的费用？

最佳答案

如果您在2个不同的查询中都不要求使用它们，则可以尝试

;with query as (
  Select Row_Number() over (Order By UserID ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
),
totalCount AS (
    SELECT COUNT(1) Total FROM query
)
Select  query.*,
        Total
from    query, totalCount
where   TableRowNum
between 1 and 25
Order By TableRowNum ASC

如果确实需要2个不同的查询，请使用表var

DECLARE @User TABLE(
        TableRowNum INT,
        FirstName VARCHAR(50),
        LastName VARCHAR(50)
)
;with query as (
  Select Row_Number() over (Order By UserID ASC) as TableRowNum,
         FirstName,
         LastName
  From   Users
)
INSERT INTO @User
SELECT  TableRowNum,
        FirstName,
        LastName
FROM    query

SELECT  *
FROM    @User
where   TableRowNum
between 1 and 25
Order By TableRowNum ASC

SELECT COUNT(1) FROM @User