多次使用一个CTE

Question

多次使用一个CTE

sqlsql-serversql-server-2008common-table-expression

92

我遇到了这个问题，在设置总数时出现了错误。为什么我不能多次访问CTE？

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 )

- gigi

6个回答

28

以上答案均不正确...您可以执行一次CTE并获得所需结果..以下是查询语句：

ALTER PROCEDURE [dbo].[GetLeaguePlayers]
(
    @idleague int,
    @pageNumber int,
    @pageSize int,
    @total int OUTPUT
)
AS
WITH CTEPlayers AS
(
    SELECT 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
),
TotalCount AS
(
 SELECT COUNT(*) AS Total FROM CTEPlayers
),
Final_Result AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber, p.Id, p.Name, t.Name AS Team,
  (SELECT Total FROM TotalCount) AS Total
    FROM CTEPlayers
)
SELECT Id, Name, @total = Total
FROM Final_Results c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;

- om471987

2

似乎这只限于只读操作。尝试使用基于第一个CTE的连接更新相同的结果集会在有效的更新语句上给我带来错误。 - Derek Greer

我在这样做时遇到了这个错误："将值分配给变量的SELECT语句不能与数据检索操作组合使用。"不过，我使用的是SQL Server 2014. - user1829319

1

其实是 SQL Server 2016。 - user1829319

1

无法工作，出现错误“将值分配给变量的SELECT语句不能与数据检索操作组合使用。” - Fooker

20

CTE（公共表达式）仅对一个语句有效。

您可以创建一个内联表值函数，然后根据需要使用它。内联函数执行其名称所示的操作；其查询将成为使用它的查询的一部分（与非内联函数相反，非内联函数将单独执行并用作行集）。

- Lucero

1

作为一名软件开发人员，我更喜欢这种方法。它使我能够将我的逻辑整合到一个函数中，然后在多个存储过程中使用它。对于复杂的查询尤其有帮助。我发现我可以返回一堆列并添加许多连接以使其可重用，但可能不是每个引用它的存储过程都需要，但是 SQL Server 会注意不处理您的存储过程未使用的额外连接和列。您还可以让内联表值函数调用其他内联表值函数，以便进一步构建基本查询逻辑！ - MikeTeeVee

5

使用多个 CTE 多次收集数据

;with CTEReminder AS
(
    Select r.ReminderID,r.IsVerificationRequired from ReminderTbl r      -- main table
),
FileTaskCountTempTbl   as     
    (
        select  COUNT(t.ReminderID) as FileTaskCount                     -- getting first result
            from TaskTbl t
                left join CTEReminder r on t.ReminderID = r.ReminderID          
    ),
FollowUpCountTempTbl  as
    (
        select COUNT(f.FollowUpID)  as Total                             -- getting second result
            from FollowUpTbl f              --cte not used here
    ),
MachineryRegularTaskCountTempTbl as
    (
        select  COUNT(t.ReminderID) as TotalCount                        -- getting third result
                from TaskTbl t
                    left join CTEReminder r on t.ReminderID = r.ReminderID                  
    ),
FinalResultTempTbl as
    (
        select COUNT(t.ReminderID)  as MachineryTaskCount,               -- getting fourth result
                (select * from MachineryRegularTaskCountTempTbl ) as MachineryRegularTaskCount,  -- Combining earlier results to last query 
                (select * from FollowUpCountTempTbl ) as FollowUpCount,   -- Combining earlier results to last query 
                (select * from FileTaskCountTempTbl ) as FileTaskCount   -- Combining earlier results to last query 
            from TaskTbl t
                left join CTEReminder r on t.ReminderID = r.ReminderID          
    )

select * from FinalResultTempTbl

- Arun Prasad E S

1

这种方法是否已知存在性能影响？我观察到，如果一个公共表达式返回大量记录，即使执行时间很快，由于提取时间较长，下一个公共表达式的性能也不是很好。我通过将第二个公共表达式的结果限制为一行来进行测试。 - bluelurker

@bluelurker 我认为需要有人花时间给出更好的意见。等待那个人的到来。 - Arun Prasad E S

@bluelurker：CTE 就像是宏，会在查询中展开。这意味着如果在查询中多次引用 CTE，则 CTE 将被计算多次。 - Panda1122

@Panda1122 怎么确定这是真的。有任何参考资料吗？ - Arun Prasad E S

1

@ArunPrasadES：分析器没有帮助吗？ - Panda1122

0

在这种情况下，我使用这个：

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,    
        COUNT(1) OVER () AS RecordCount,
    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 RowNumber,
    CAST(CEILING(CAST(RecordCount AS FLOAT) / CAST(@pageSize AS FLOAT)) AS INT) PageCount,
    RecordCount,
    Id, 
    Name
FROM CTEPlayers c
WHERE RowNumber > @pageSize*(@pageNumber-1) AND RowNumber < @pageSize*@pageNumber;

- Hugo Valer

0

将输出存储在临时表中，同时包括总计数；设置输出变量值并从临时表返回所需列

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
),
TotalCounter(TotalRecords) as
(select count(1) from CTEPlayers)


SELECT Id, Name, TotalRecords(select TotalRecords from TotalCounter) into #tmp
FROM CTEPlayers c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;

SET @total = ( SELECT TotalRecords FROM #tmp)

select Id, Name from $tmp

drop table #tmp

- Mushfiq Shaikh

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JNK · Accepted Answer

一个 CTE 基本上就是一个一次性的视图。它只存在于单个语句中，然后自动消失。

您的选项包括：

重新定义CTE 第二次。这只需要从WITH...开始到定义结束再复制粘贴到 SET之前。
将结果放入一个 #temp表格或者 @table变量
将结果实例化为一个真正的表，并引用那里的内容
稍微修改一下，只需从您的 CTE 中 SELECT COUNT：

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