多次使用一个CTE [英] Use one CTE many times

问题描述

我有这个，在设定的总数时我得到一个错误。
为什么我不能多次访问cte？

I have this, and i get an error at set total. Why can't i access a cte many times?

ALTER PROCEDURE [dbo].[GetLeaguePlayers]
(
    @idleague int,
    @pageNumber int,
    @pageSize int,
    @total int OUTPUT
)
AS
WITH CTEPlayers AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber, p.Id, p.Name, t.Name AS Team
    FROM Players p INNER JOIN Teams t ON p.IdTeam=t.Id INNER JOIN Leagues l ON l.Id=t.IdLeague
    WHERE l.Id=@idleague
)
SELECT Id, Name
FROM CTEPlayers c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;
SET @total = ( SELECT COUNT(*) FROM CTEPlayers )

推荐答案

CTE 基本上是一个一次性视图。它只保留一个语句，然后自动消失。

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.

您的选项包括：

• 第二次重新定义 CTE 。这就像从 WITH ... 到定义末尾到 SET 之前的复制粘贴一样简单。

• Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET.

将结果放入 #temp 表或 @table 变量

将结果材料化为真实表并引用

Materialize the results into a real table and reference that

从CTE中略微更改为 SELECT COUNT ：

Alter slightly to just SELECT COUNT from your CTE:

。

SELECT @total = COUNT(*)
FROM Players p 
INNER JOIN Teams t 
    ON p.IdTeam=t.Id 
INNER JOIN Leagues l 
    ON l.Id=t.IdLeague
WHERE l.Id=@idleague

这篇关于多次使用一个CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！