sql server - recursive - ¿Cómo hacer referencia a un CTE dos veces?

sql server cte recursive

Question

Tengo una expresión de tabla común muy grande que incluye números de fila para que pueda devolver un conjunto de resultados paginado. También quiero devolver el número total de registros que coinciden con la consulta antes de buscar el conjunto de resultados.

with recs as (select *, row_number() over (order by id) as rownum from ......)
select * from recs where rownum between @a and @b .... select count(*) from recs

Obviamente, mi consulta anterior es irregular, pero es solo para ilustrar mi punto. Quiero una página de resultados Y el número total de coincidencias. ¿Cómo hago esto sin tener que copiar y pegar literalmente la CTE de más de 20 líneas?

Answer 1

Así es como manejamos la paginación (sin administración de sesión por ahora) en un entorno de producción. Realiza como se espera.

DECLARE
   @p_PageNumberRequested  int = 1,
      -- Provide -1 to retreive all pages with all the rows.
   @p_RowsPerPage          int = 25

;WITH Numbered AS (
SELECT
   ROW_NUMBER() OVER (ORDER BY YourOrdering) AbsoluteRowNumber
,  COUNT(1) OVER () TotalRows
,  YourColumns
FROM
   YourTable
),
Paged AS (
SELECT
   (AbsoluteRowNumber - 1) / @p_RowsPerPage + 1 PageNumber,
   *
FROM
   Numbered)
SELECT
   ROW_NUMBER() OVER(PARTITION BY PageNumber ORDER BY AbsoluteRowNumber) RowNumberOnPage,
   *
FROM
   Paged
WHERE
      PageNumber = @p_PageNumberRequested
   OR
      @p_PageNumberRequested = -1
ORDER BY 
   AbsoluteRowNumber

Answer 2

Esto es lo mejor:

;WITH recs AS
(SELECT a,b,c,
      row_number() over (
                         ORDER BY id) AS RowNum,
                   row_number() over () AS RecordCount
FROM ......)
SELECT a,b,c,rownum,RecordCount FROM recs
WHERE rownum BETWEEN @a AND @b

Answer 3

No pienses que puedes. Desde MSDN

Una expresión de tabla común (CTE) se puede considerar como un conjunto de resultados temporal que se define dentro del alcance de ejecución de una sola instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW.

Énfasis en "una sola instrucción SELECT, INSERT, UPDATE, DELETE, o CREATE VIEW".

Esta podría ser una situación en la que desee utilizar una tabla temporal .

CREATE TABLE #Recs
{
  .....
}
INSERT INTO #Recs
select *, row_number() over (order by id) as rownum from ......

Si no conoce la estructura de la tabla de antemano, puede usar este formulario para crear una tabla temporal:

select *, row_number() over (order by id) as rownum INTO #Recs from ......

Podrá usar la tabla temporal de la manera que ha descrito anteriormente.

Answer 4

Podría añadir un campo que tenga el total de filas, por supuesto que estará en cada fila

select recs.*,totalrows = (select count(0) from recs) 
from recs

Answer 5

Puede usar comas para crear múltiples CTE que hagan referencia a los CTE anteriores.

Solo para ilustrar lo que quiero decir:

with recs as (
select 
    *, 
    row_number() over (order by id) as rownum from ......
    ),
counts as (
    select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts 
where rownum between @a and @b .... 

Esta no es una buena solución.

La mejor solución que encontré para tener el recuento total en un CTE sin contar los registros se describe en este artículo .

DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq