하나의 CTE를 여러 번 사용

하나의 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 )

A CTE기본적으로 일회용 보기입니다.단일 문만 유지된 후 자동으로 사라집니다.

옵션은 다음과 같습니다.

• 정의를 다시 정의합니다.CTE두 번째이것은 복사 붙여넣기처럼 간단합니다.WITH...정의의 끝을 통해 당신의 앞까지.SET.

• 결과를 에 저장합니다.#temp테이블 또는 a@table변수

• 결과를 실제 표로 구체화하고 다음을 참조합니다.

• 정의로 약간 변경SELECT COUNTCTE에서:

.

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를 한 번 실행하여 원하는 결과를 얻을 수 있습니다.여기 질문이 있습니다.

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는 정의에 따라 하나의 문에만 유효합니다.

인라인 테이블 값 함수를 만든 다음 원하는 빈도로 사용할 수 있습니다.인라인 함수는 이름이 제안하는 것을 수행합니다. 인라인 함수는 별도로 실행되어 행 집합으로 사용되는 비 인라인 함수와 달리 쿼리의 일부가 됩니다.

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 

이 경우 다음을 사용합니다.

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;

총 카운트와 함께 출력을 임시 테이블에 저장합니다. 출력 변수 값을 설정하고 임시 테이블에서 필요한 열을 반환합니다.

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

언급URL : https://stackoverflow.com/questions/10196808/use-one-cte-many-times