Equivalente di LIMITE e di OFFSET per SQL Server?

In PostgreSQL c’è il Limit e Offset parole chiave che permetterà molto facile impaginazione del set di risultati.

Che cosa è l’equivalente di sintassi di Sql Server?

  • Per sql server 2012, questa funzionalità è implementata in modo semplice. Vedere la mia risposta
  • Grazie per questa domanda, siamo costretti a transizione da MySQL a MsSQL 🙁
InformationsquelleAutor Earlz | 2010-01-25

 

15 Replies
  1. 133

    L’equivalente di LIMIT è SET ROWCOUNT, ma se volete generico impaginazione è meglio scrivere una query come questa:

    ;WITH Results_CTE AS
    (
        SELECT
            Col1, Col2, ...,
            ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
        FROM Table
        WHERE <whatever>
    )
    SELECT *
    FROM Results_CTE
    WHERE RowNum >= @Offset
    AND RowNum < @Offset + @Limit

    Qui il vantaggio è la parametrizzazione dell’offset e del limite nel caso in cui si decidesse di cambiare il paging opzioni (o consentire all’utente di farlo).

    Nota: il @Offset parametro deve utilizzare una base di indicizzazione per questo, piuttosto che il normale indicizzazione in base zero.

    • Vecchio ormai. Sql Server 2012 e, successivamente, di supporto OFFSET/RECUPERO
    • Non vecchio. Ho appena ricevuto assegnato al progetto utilizzando SLQ Server 2008, avendo usato solo mysql nel passato…
    • Questo è abbastanza buono, ma deve essere ajusted un po ‘ di WHERE RowNum >= (@Offset + 1)
    • Si prega di fare riferimento alla nota alla fine di questa risposta.
    • The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. MSSQL2008 R2.
    • Ordine entro non, in quanto CON purtroppo.
    • non complicare ancora di più.. basta usare WHERE RowNum > @Offset e ci sta bene con la condizione di avvio.
    • Se il mio Table ha 200k record, verrà recuperato tutti di prima, quindi applicare il limite ? È questa query efficiente ?
    • qualcuno sa come fare left join su questa query?
    • RowNum >= (@Offset+1) E RowNum < (@Offset+1) + @Limite

  2. 210

    Questa funzione è ora facile in SQL Server 2012.
    Questo è il lavoro da SQL Server 2012 in poi.

    Limite con offset per selezionare da 11 a 20 righe di SQL Server:

    SELECT email FROM emailTable 
    WHERE user_id=3
    ORDER BY Id
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
    • OFFSET: numero di saltato le righe
    • NEXT: numero di righe successive

    Di riferimento: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017

    • C’è un equiv SQL_CALC_FOUND_ROWS quando si utilizza questo?
    • darà che penso
    • questo dovrebbe essere la risposta corretta
    • La risposta migliore è questa!!!
    • NOTA: non È possibile utilizzare dall’interno di una CTE. Deve essere utilizzato nella query principale. Ho voluto limitare la quantità di righe restituite (impaginazione) e quindi eseguire un costoso calcolo per il 10 o giù di righe restituite, piuttosto che determinare le righe, eseguire le costose operazioni di calcolo e quindi salta/prendere quello che mi serviva. @Aaronaught la risposta di lavoro per chi ha la necessità di limitare le righe all’interno di una CTE.
    • Muluk Questo offset e fetch sta prendendo un sacco di tempo per il più alto volume di dati esempio con un offset di 1000000. Come posso fare con questo.
    • Questo non è di Offset e di Recuperare problema. Si dovrebbe rivedere l’architettura di ora il tuo tavolo. Considera il Partizionamento delle tabelle, i dati di riga e colonna diverse tipologie e totale tabella dimensioni, considerare l’archiviazione di alcune righe, se non richiesto regolarmente, controllare le specifiche del server.

  3. 23
    select top {LIMIT HERE} * from (
          select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n 
          from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}
    ) xx where r_n_n >={OFFSET HERE}

    Una nota:
    Questa soluzione funziona solo in SQL Server 2005 o superiore, dal momento che questo è stato quando ROW_NUMBER() è stato attuato.

    • Sto utilizzando questa query per un po ‘ di tempo ora e funziona benissimo, quindi grazie per questo. Mi chiedo solo ciò che il ” xx ” rappresenta?
    • il sub query richiede un nome. come io non lo uso solo mettere xx c’
    • Il xx è solo un alias di tabella. Potrebbe essere un po ‘ più chiara, se hai detto AS xx
    • Grazie funziona per me
    • qualcuno sa come fare a left join su questa query?
  4. 12

    È possibile utilizzare la funzione ROW_NUMBER in un’Espressione di Tabella Comune per raggiungere questo obiettivo.

    ;WITH My_CTE AS
    (
         SELECT
              col1,
              col2,
              ROW_NUMBER() OVER(ORDER BY col1) AS row_number
         FROM
              My_Table
         WHERE
              <<<whatever>>>
    )
    SELECT
         col1,
         col2
    FROM
         My_CTE
    WHERE
         row_number BETWEEN @start_row AND @end_row
    • Questo dovrebbe essere accettato risposta.Perfetto
  5. 3

    Per me l’uso di OFFSET e di RECUPERARE insieme, lento, così ho usato una combinazione di TOP e OFFSET come questo (che era più veloce):

    SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
        WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

    Nota: Se si utilizza la parte SUPERIORE e OFFSET insieme nella stessa query come:

    SELECT TOP 20 columname1, columname2 FROM tablename
        WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS

    Si ottiene un errore, quindi per usare TOP e OFFSET insieme è necessario separare con un sub-query.

    E se è necessario utilizzare SELECT DISTINCT quindi la query è la seguente:

    SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
        WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

    Nota: L’uso di SELEZIONARE ROW_NUMBER con DISTINTE non ha funzionato per me.

    • Ho “UNA TOP non può essere utilizzato nella stessa query o sub-query come OFFSET.”
    • Hai ragione @MichaelRushton, non possono essere usati nella stessa query o nella stessa sub-query, quindi è necessario utilizzare un sub-query, separato. Quindi, se si dispone di SQL come SELECT TOP 20 id FROM table1 where id > 10 order by date OFFSET 20 rows, è necessario trasformarlo come SELECT TOP 20 * FROM (SELECT id FROM table1 where id > 10 order by date OFFSET 20 ROWS) t1. Vorrei modificare la mia risposta. Grazie e scusa il mio inglese.
  6. 2

    Un altro esempio :

    declare @limit int 
    declare @offset int 
    set @offset = 2;
    set @limit = 20;
    declare @count int
    declare @idxini int 
    declare @idxfim int 
    select @idxfim = @offset * @limit
    select @idxini = @idxfim - (@limit-1);
    WITH paging AS
        (
            SELECT 
                 ROW_NUMBER() OVER (order by object_id) AS rowid, *
            FROM 
                sys.objects 
        )
    select *
        from 
            (select COUNT(1) as rowqtd from paging) qtd, 
                paging 
        where 
            rowid between @idxini and @idxfim
        order by 
            rowid;
    • Ho rimosso il tuo anti-microsoft odio. Non parliamo di guerre sante qui; basta rispondere e porre domande non in modo soggettivo.
  7. 2

    C’è qui qualcuno che dice che su questa funzionalità in sql 2011, la sua triste un po ‘ diverso per parola chiave “OFFSET /FETCH”, ma non è standard, quindi ok.

  8. 2

    L’aggiunta di una leggera variazione sul Aaronaught soluzione, io di solito parametrizzare il numero di pagina (@PageNum) e le dimensioni della pagina (@PageSize). In questo modo ogni pagina evento click appena inviato il numero della pagina richiesta con un modulo configurabile dimensioni della pagina:

    begin
        with My_CTE  as
        (
             SELECT col1,
                  ROW_NUMBER() OVER(ORDER BY col1) AS row_number
         FROM
              My_Table
         WHERE
              <<<whatever>>>
        )
        select * from My_CTE
                WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1) 
                                  AND @PageNum * @PageSize
    
    end
  9. 2

    Il più vicino ho potuto fare è

    select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber  and ct <= toNumber

    Che immagino simile a select * from [db].[dbo].[table] LIMIT 0, 10

    • funziona grazie.
  10. 1
    select top (@TakeCount) * --FETCH NEXT
    from(
        Select  ROW_NUMBER() OVER (order by StartDate) AS rowid,*
        From YourTable
    )A
    where Rowid>@SkipCount --OFFSET
  11. 1
    @nombre_row :nombre ligne par page  
    @page:numero de la page
    
    //--------------code sql---------------
    
    declare  @page int,@nombre_row int;
        set @page='2';
        set @nombre_row=5;
        SELECT  *
    FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY etudiant_ID ) AS RowNum, *
          FROM      etudiant
    
        ) AS RowConstrainedResult
    WHERE   RowNum >= ((@page-1)*@nombre_row)+1
        AND RowNum < ((@page)*@nombre_row)+1
    ORDER BY RowNum
  12. 1

    Visto che nessuno ha fornito questo codice di sicurezza:

    SELECT TOP @limit f1, f2, f3...
    FROM t1
    WHERE c1 = v1, c2 > v2...
    AND
        t1.id NOT IN
            (SELECT TOP @offset id
             FROM t1
             WHERE c1 = v1, c2 > v2...
             ORDER BY o1, o2...)
    ORDER BY o1, o2...

    Punti importanti:

    • ORDINE deve essere identico
    • @limit può essere sostituito con il numero di risultati da recuperare,
    • @offset è il numero di risultati per saltare
    • Si prega di confrontare le prestazioni con le precedenti soluzioni come possono essere più efficienti
    • questa soluzione duplicati where e order by clausole, e fornire risultati errati se sono fuori sync
    • invece order by c’è esplicitamente se questo è ciò che è necessario
  13. 1
    -- @RowsPerPage  can be a fixed number and @PageNumber number can be passed 
    DECLARE @RowsPerPage INT = 10, @PageNumber INT = 2
    
    SELECT *
    
    FROM MemberEmployeeData
    
    ORDER BY EmployeeNumber
    
    OFFSET @PageNumber*@RowsPerPage ROWS
    
    FETCH NEXT 10 ROWS ONLY
  14. -2
    SELECT * FROM [dbo].[TableName] Order By  [dbo].[TableName].FieldName DESC
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY
    • Stavo solo dando un equivalente di limite e di offset, la soluzione di cui sopra ha funzionato per me. Se volete top 10 solo dire offset 0 e Fetch next 10 righe.

Lascia un commento