多次使用一个 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 )
104581 次浏览
小开

根据定义，CTE 只对一个语句有效。

您可以创建一个 内联表值函数内联表值函数，然后随时使用它。内联函数完成名称所暗示的任务; 它的查询成为使用它的查询的一部分(与非内联函数相反，非内联函数是单独执行的，并作为行集使用)。

小开
最佳答案

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
小开

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

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;
小开

以上所有的答案都不正确... 您可以执行一次 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;
小开

使用 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

enter image description here

小开

将输出与总计数一起存储在临时表中; 设置输出变量值并从临时表中返回所需的列

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

提交答案