如何重复使用公共表达式（Common Table Expression）？

Question

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

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，查询数据，然后查询计数），但是当我这样做时，我会收到一个错误消息“无效的对象名称'query'”，以响应第二个查询（计数）。

有没有办法将这两个查询组合成一个，以节省到数据库的往返？

- Yaakov Ellis

Answer 1

如果您不需要使用两个不同的查询，可以尝试：

;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

如果你确实需要两个不同的查询，最好使用表变量

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

- Adriaan Stander

---

我发现使用公用表表达式（CTE）更易于阅读、重复使用和维护。 - Adriaan Stander

哦，谢谢。在你的第一个代码块中，你使用了CTE来获取总数和结果，这很有道理。但是当我尝试两次查询CTE时，会出现错误。你能解释一下为什么这样做可以吗？ - Scott Rippey

+1. 我正在处理三个大表（2000万以上）的联合查询，第一个示例导致查询时间增加了一倍。我使用了第二个示例，性能非常接近没有总计的查询；扫描内存表会有一点性能损失，但很小（大约一秒钟）。 - Justin Helgerson

Answer 2

你可以这样做：

with query as (
   Select 
 COUNT (*) OVER (PARTITION BY 1) AS TableTotalRows,
 Row_Number() over (Order By OrderNum ASC) as TableRowNum,
     FirstName,
     LastName
  From   Users
)

- Anzar

---

如果您在代码行前添加4个空格，它将被Stack Overflow识别为代码。 - Gamlor

有时查询本身可能包含聚合函数（FirstName，LastName只是一个简单的例子），因此将Count(*)添加为列不是可靠的解决方案。 - Yaakov Ellis