代码之家 › 专栏 › 技术社区 › Yaakov Ellis NevilleDNZ

如何重用公共表表达式

common-table-expression sql-server sql

Yaakov Ellis NevilleDNZ · 技术社区 · 15 年前

我正在使用一个公共表表达式进行分页:

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,查询数据,然后查询计数,但是当我这样做时,在响应第二个查询(计数)时,会收到一条错误消息“invalid object name‘query’”。

是否有任何方法可以将这两个查询合并为一个查询,从而节省到数据库的往返时间?

3 回复 | 直到 12 年前

Adriaan Stander 15 年前

如果在两个不同的查询中不需要它们,可以尝试

;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

Gamlor 13 年前

你可以这样做:

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

Javier Gonzalez 12 年前

根据微软的说法 link :

CTE可以引用自身和以前定义的CTE 附子句。

在引用先前定义的CTE的新CTE中,我们可以进行计数查询:

;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

“query”是主CTE,“totalCount”使用它来获取总行数

微软应该有这样一个常见任务的例子。