sql-server - 如何引用一个 CTE 两次？

Question

我有一个非常丰富的公用表表达式，其中包含行号，以便我可以返回分页结果集。我还想在对结果集进行分页之前返回与查询匹配的记录总数。

with recs as (select *, row_number() over (order by id) as rownum from ......)
select * from recs where rownum between @a and @b .... select count(*) from recs

显然我上面的查询是不完整的，但这只是为了说明我的观点。我想要一页结果和匹配总数。如何做到这一点，而不必逐字复制并粘贴整个 20 多行 CTE？

Answer 1

别以为你可以。来自 MSDN

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

强调“单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句。”

在这种情况下，您可能需要使用 Temporary Table .

CREATE TABLE #Recs
{
  .....
}
INSERT INTO #Recs
select *, row_number() over (order by id) as rownum from ......

如果您事先不知道表的结构，您可以使用此表单创建临时表:

select *, row_number() over (order by id) as rownum INTO #Recs from ......

您将能够按照上述方式使用临时表。