Utilisez un CTE plusieurs fois

Question

Utilisez un CTE plusieurs fois

J'ai ceci, et je reçois une erreur à set total. Pourquoi ne puis-je pas accéder à un cte plusieurs fois?

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 )

36

common-table-expression sql sql-server sql-server-2008

demandé sur RBarryYoung 2012-04-17 22:22:15

4 réponses

score 53 · Answer 1

Un CTE est fondamentalement une seule vue. Il ne persiste que pour une seule instruction, puis disparaît automatiquement.

Vos options incluent:

Redéfinissez le CTE une seconde fois. Ceci est aussi simple que copier-coller de WITH... à la fin de la définition avant votre SET.
Mettez vos résultats dans un #temp table ou un @table variable
Matérialiser les résultats dans un tableau réel et de référence que
Modifier légèrement à seulement SELECT COUNT de votre 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

score 12 · Answer 2

Un CTE n'est, par définition, valable que pour une instruction.

Vous pouvez créer une fonction à valeur de table en ligne , puis l'utiliser aussi souvent que vous le souhaitez. La fonction en ligne fait ce que le nom suggère; sa requête fait partie de la requête qui l'utilise (contrairement aux fonctions non en ligne qui sont exécutées séparément et utilisées comme un jeu de lignes).

score 12 · Answer 3

Aucune des réponses ci-dessus n'est correcte... Vous pouvez exécuter CTE une fois et obtenir le résultat souhaité.. voici la requête

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;

score 0 · Answer 4

Dans ce cas, j'utilise ceci:

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;

Las etiquetas más populares

Utilisez un CTE plusieurs fois

4 réponses