Equivalente di LIMIT e OFFSET per SQL Server?

In PostgreSQL ci sono le parole chiave Limit e Offset che permetteranno una facile impaginazione dei set di risultati.

Qual è la syntax equivalente per Sql Server?

L’equivalente di LIMIT è SET ROWCOUNT , ma se vuoi l’impaginazione generica è 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  ) SELECT * FROM Results_CTE WHERE RowNum >= @Offset AND RowNum < @Offset + @Limit 

Il vantaggio qui è la parametrizzazione dell'offset e del limite nel caso in cui si decida di modificare le opzioni di paging (o consentire all'utente di farlo).

Nota: il parametro @Offset deve utilizzare l'indicizzazione basata su uno per questo piuttosto che sulla normale indicizzazione basata su zero.

Questa funzionalità è ora semplificata in SQL Server 2012. Funziona da SQL Server 2012 in poi.

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

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

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

 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 funzionerà solo in SQL Server 2005 o versioni successive, poiché questo era il momento in cui ROW_NUMBER() stato implementato.

Puoi utilizzare ROW_NUMBER in un’espressione di tabella comune per ottenere ciò.

 ;WITH My_CTE AS ( SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) AS row_number FROM My_Table WHERE <<>> ) SELECT col1, col2 FROM My_CTE WHERE row_number BETWEEN @start_row AND @end_row 

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; 

C’è qualcuno che parla di questa funzionalità in sql 2011, è triste che scelgano una parola chiave leggermente diversa “OFFSET / FETCH” ma non è standard quindi ok.

Aggiungendo una leggera variazione alla soluzione di Aaronaught, di solito parametrizzo il numero di pagina (@PageNum) e la dimensione della pagina (@PageSize). In questo modo, ogni evento click della pagina invia semplicemente il numero di pagina richiesto insieme a una dimensione della pagina configurabile:

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

Il più vicino che potrei fare è

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

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

Per me l’uso di OFFSET e FETCH insieme è stato lento, quindi ho usato una combinazione di TOP e OFFSET come questa (che era più veloce):

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

Nota: se si utilizza TOP e OFFSET insieme nella stessa query come:

 SELECT TOP 20 columname1, columname2 FROM tablename WHERE  ORDER BY columname1 OFFSET 100 ROWS 

Quindi si verifica un errore, quindi per utilizzare TOP e OFFSET insieme è necessario separarlo con una sottoquery.

E se hai bisogno di usare SELECT DISTINCT allora la query è come:

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

Nota: l’utilizzo di SELECT ROW_NUMBER con DISTINCT non ha funzionato per me.

 @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 

Poiché nessuno ha ancora fornito questo codice:

 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:

  • L’ordine BY deve essere identico
  • @limit può essere sostituito con il numero di risultati da recuperare,
  • @offset è il numero di risultati da saltare
  • Si prega di confrontare le prestazioni con le soluzioni precedenti in quanto potrebbero essere più efficienti
  • questa soluzione duplica where e order by clausole e fornirà risultati errati se non sono sincronizzati
  • d’altra parte l’ order by è lì esplicitamente se è ciò che è necessario
 -- @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 

Nel server SQL utilizzerai TOP insieme a ROW_NUMBER ()

 select top (@TakeCount) * --FETCH NEXT from( Select ROW_NUMBER() OVER (order by StartDate) AS rowid,* From YourTable )A where Rowid>@SkipCount --OFFSET