Offset riga in SQL Server

C’è un modo in SQL Server per ottenere i risultati a partire da un dato offset? Ad esempio, in un altro tipo di database SQL, è ansible fare:

SELECT * FROM MyTable OFFSET 50 LIMIT 25 

per ottenere risultati 51-75. Questo costrutto non sembra esistere in SQL Server.

Come posso realizzare questo senza caricare tutte le righe che non mi interessa? Grazie!

Eviterei di usare SELECT * . Specifica le colonne che vuoi effettivamente anche se potrebbero essere tutte.

SQL Server 2005+

 SELECT col1, col2 FROM ( SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow 

SQL Server 2000

Paging efficiente attraverso set di risultati di grandi dimensioni in SQL Server 2000

Un metodo più efficiente per sfogliare set di risultati di grandi dimensioni

Se si stanno elaborando tutte le pagine per poi semplicemente ricordare l’ultimo valore chiave visto nella pagina precedente e usando TOP (25) ... WHERE Key > @last_key ORDER BY Key può essere il metodo più performante se esistono indici adatti per consentire questo deve essere cercato in modo efficiente o un cursore API se non lo fanno.

Per selezionare una pagina arbitraria, la soluzione migliore per SQL Server 2005 – 2008 R2 è probabilmente ROW_NUMBER e BETWEEN

Per SQL Server 2012+ è ansible utilizzare la clausola ORDER BY migliorata per questa esigenza.

 SELECT * FROM MyTable ORDER BY OrderingColumn ASC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

Anche se resta da vedere quanto sarà performante questa opzione .

Questo è un modo (SQL2000)

 SELECT * FROM ( SELECT TOP (@pageSize) * FROM ( SELECT TOP (@pageNumber * @pageSize) * FROM tableName ORDER BY columnName ASC ) AS t1 ORDER BY columnName DESC ) AS t2 ORDER BY columnName ASC 

e questo è un altro modo (SQL 2005)

 ;WITH results AS ( SELECT rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC ) , * FROM tableName ) SELECT * FROM results WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize 

Puoi utilizzare la funzione ROW_NUMBER() per ottenere ciò che desideri:

 SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t WHERE RowNr BETWEEN 10 AND 20 

Per le tabelle con colonne di dati più grandi, preferisco:

 SELECT tablename.col1, tablename.col2, tablename.col3, ... FROM ( ( SELECT col1 FROM ( SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum FROM tablename WHERE ([CONDITION]) ) AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT] ) AS T2 INNER JOIN tablename ON T2.col1=tablename.col1 ); 

 [CONDITION] can contain any WHERE clause for searching. [OFFSET] specifies the start, [LIMIT] the maximum results. 

Offre prestazioni molto migliori su tabelle con dati di grandi dimensioni come BLOB, perché la funzione ROW_NUMBER deve solo esaminare una colonna e solo le righe corrispondenti vengono restituite con tutte le colonne.

C’è OFFSET .. FETCH in SQL Server 2012, ma dovrai specificare una colonna ORDER BY .

Se davvero non hai alcuna colonna esplicita che potresti passare come colonna ORDER BY (come suggerito da altri), puoi utilizzare questo trucco:

 SELECT * FROM MyTable ORDER BY @@VERSION OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

… o

 SELECT * FROM MyTable ORDER BY (SELECT 0) OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

Lo stiamo utilizzando in jOOQ quando gli utenti non specificano esplicitamente un ordine. Questo produrrà quindi ordinamenti piuttosto casuali senza costi aggiuntivi.

Vedi la mia selezione per cercapersone

 SELECT TOP @limit * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM ( -- YOU SELECT HERE SELECT * FROM mytable ) myquery ) paginator WHERE offset > @offset 

Questo risolve l’impaginazione;)

 SELECT TOP 75 * FROM MyTable EXCEPT SELECT TOP 50 * FROM MyTable 

A seconda della tua versione, non puoi farlo direttamente, ma potresti fare qualcosa di simile a hacky

 select top 25 * from ( select top 75 * from table order by field asc ) a order by field desc 

dove ‘campo’ è la chiave.

Dovresti fare attenzione quando usi l’istruzione row_number () OVER (ORDER BY) poichè performane è piuttosto scadente. Lo stesso vale per l’utilizzo di Common Table Expressions con row_number () che è anche peggio. Sto utilizzando il seguente frammento di codice che è risultato essere leggermente più veloce, quindi utilizzando una variabile di tabella con un’identity framework per fornire il numero di pagina.

 DECLARE @Offset INT = 120000 DECLARE @Limit INT = 10 DECLARE @ROWCOUNT INT = @[email protected] SET ROWCOUNT @ROWCOUNT SELECT * FROM MyTable INTO #ResultSet WHERE MyTable.Type = 1 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY SortConst ASC) As RowNumber FROM ( SELECT *, 1 As SortConst FROM #ResultSet ) AS ResultSet ) AS Page WHERE RowNumber BETWEEN @Offset AND @ROWCOUNT DROP TABLE #ResultSet 

Di seguito verranno visualizzati 25 record, esclusi i primi 50 lavori di record in SQL Server 2012.

 SELECT * FROM MyTable ORDER BY ID OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; 

puoi sostituire l’ID come requisito

Io uso questa tecnica per l’impaginazione. Non prendo tutte le righe. Ad esempio, se la mia pagina deve visualizzare le prime 100 righe, recupero solo la clausola 100 con where. L’output di SQL dovrebbe avere una chiave univoca.

La tabella ha il seguente:

 ID, KeyId, Rank 

Lo stesso rank sarà assegnato per più di un KeyId.

SQL è select top 2 * from Table1 where Rank >= @Rank and ID > @Id

Per la prima volta passo 0 per entrambi. La seconda volta passa 1 e 14. 3a volta passa 2 e 6 ….

Il valore del decimo record Rank & Id viene passato al successivo

 11 21 1 14 22 1 7 11 1 6 19 2 12 31 2 13 18 2 

Questo avrà il minimo stress sul sistema

In SqlServer2005 è ansible effettuare le seguenti operazioni:

 DECLARE @Limit INT DECLARE @Offset INT SET @Offset = 120000 SET @Limit = 10 SELECT * FROM ( SELECT row_number() OVER (ORDER BY column) AS rownum, column2, column3, .... columnX FROM table ) AS A WHERE A.rownum BETWEEN (@Offset) AND (@Offset + @Limit-1) 

Ho cercato questa risposta per un po ‘ora (per le query generiche) e ho scoperto un altro modo di farlo su SQL Server 2000+ utilizzando ROWCOUNT e cursori e senza TOP o alcuna tabella temporanea.

Usando SET ROWCOUNT [OFFSET+LIMIT] puoi limitare i risultati e, con i cursori, andare direttamente alla riga che desideri, quindi continuare fino alla fine.

Quindi la tua domanda sarebbe come questa:

 SET ROWCOUNT 75 -- (50 + 25) DECLARE MyCursor SCROLL CURSOR FOR SELECT * FROM pessoas OPEN MyCursor FETCH ABSOLUTE 50 FROM MyCursor -- OFFSET WHILE @@FETCH_STATUS = 0 BEGIN FETCH next FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor SET ROWCOUNT 0 

Il modo migliore per farlo senza perdere tempo per ordinare i record è come questo:

 select 0 as tmp,Column1 from Table1 Order by tmp OFFSET 5000000 ROWS FETCH NEXT 50 ROWS ONLY 

ci vuole meno di un secondo!
la migliore soluzione per tavoli di grandi dimensioni.