Início / dba / Perguntas / 120977
Accepted
AA.SC
Asked: 2015-11-14 01:39:32 +0800 CST

Paginação do SQL Server usando CTE

  • 772

Eu tenho que aplicar paginação na consulta conforme o requisito, preciso de duas colunas extras além do resultado da consulta
1. Total de registros
2. Total de páginas

mas estou tentando confirmar que este é o caminho certo para recuperar o número total de páginas 

CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage

WITH CTE 
AS
(
        SELECT COUNT(*) OVER() TotalRec, ROW_Number() over(ORDER BY id) RowNo, ColumnOne, ColumnTwo
        FROM
        (           
        SELECT ColumnOne, ColumnTwo
        FROM table1 ct LEFT OUTER JOIN table2 IR ON ct.code=IR.code                         
        WHERE table1.code=3
        Group By  ColumnOne, ColumnTwo

        )ImmCat
)

    SELECT TotalRec TotalRec, 
    CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage 
    ,ColumnOne, ColumnTwo
    FROM CTE
    WHERE RowNo BETWEEN (((@PageNo - 1)*@PageSize) + 1)) AND (@PageNo * @PageSize))
sql-server sql-server-2008-r2

3 respostas

  1. Best Answer

    Você certamente pode evitar uma condicional aqui, mas também não precisa de CEILINGconversão de ou numericpara obter o número total de páginas se ambos @totalrece @pagesizeforem inteiros (e parece seguro assumir que são). A fórmula a seguir fornecerá o número de páginas para acomodar @totalrecas linhas, dado o tamanho da página de @pagesize:

    (@totalrec - 1) / @pagesize + 1 AS TotalPages
    • 2

  2. Você tem vários erros de digitação, uma referência ambígua, possível div/0 e não sei dizer se os parâmetros de entrada são zero ou um, então isso pode precisar de alguns ajustes. Eu faria os parâmetros @TotalPage e @TotalRec OUTPUT, mas para simplificar:

    EDIT: Corrigido um nome de variável, parênteses adicionados, alterados para assumir a base zero.

    set @TotalRec = (select count(id) from table1 where code = 3)

if @TotalRec > 0 and @PageSize > 0
    set @TotalPage = @TotalRec/(@PageSize + 1)

set rowcount @PageSize

;with cte as (
    select
         row_number() over (order by id) as RowNo
        ,ColumnOne
        ,ColumnTwo
    from
        table1 ct
    left outer join table2 IR
        on ct.code=IR.code
    where ???.code = 3
)
select
     @TotalRec as TotalRec
    ,@TotalPage as TotalPage
    ,ColumnOne
    ,ColumnTwo
from cte
where RowNo > @PageNo * @PageSize
order by RowNo -- THIS IS IMPORTANT! DO NOT OMIT!
    • 1

  3. Existem duas maneiras a seguir para obter o número total de páginas, recomendamos primeiro.

    1. ceiling(convert(numeric(9,4),@totalrec)/convert(numeric(9,4),@pagesize)) as TotalPage

    2. CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END TotalPage

    • 0

relate perguntas