stránkování se často používá v aplikacích, kde uživatel může klepnutím na Předchozí / Další procházet stránky, které tvoří výsledky, nebo kliknutím na číslo stránky přejdete přímo na konkrétní stránku.
Při spuštění dotazů v SQL Server, můžete stránkovat výsledky pomocí OFFSETFETCH argumenty ORDER BY klauzule. Tyto argumenty byly zavedeny v SQL Server 2012, proto můžete tuto techniku použít, pokud máte SQL Server 2012 nebo vyšší.
V této souvislosti, stránkování je místo, kde můžete rozdělit výsledky dotazu na menší kousky, každý kus pokračuje tam, kde předchozí skončil. Pokud například dotaz vrátí 1000 řádků, můžete je stránkovat tak, aby byly vráceny ve skupinách po 100. Aplikace může předat číslo stránky a velikost stránky serveru SQL Server a SQL Server jej pak může použít k vrácení pouze dat pro požadovanou stránku.
Příklad 1 – žádné stránkování
nejprve spusťte dotaz, který vrátí všechny řádky v tabulce:
SELECT *FROM GenresORDER BY GenreId;
výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
tento příklad nepoužívá stránkování-zobrazí se všechny výsledky.
Tento výsledek je tak malý, že by za normálních okolností vyžadují stránkování, ale pro účely tohoto článku, pojďme stránkování.
Příklad 2 – Zobrazení První 3 Výsledky
Tento příklad zobrazuje prvních tří výsledků:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
V tomto případě, jsem upřesnit, že na výsledky by se mělo začít na první výsledek a zobrazí se další tři řádky. To se provádí pomocí následujícího:
-
OFFSET 0 ROWSurčuje, že by neměl být žádný offset (posun nula). -
FETCH NEXT 3 ROWS ONLYzíská další tři řádky z offsetu. Protože jsem zadal posun nuly, první tři řádky jsou načteny.
Pokud jsme chtěli pouze výsledky top 3, mohli jsme dosáhnout stejného výsledku pomocí klauzule TOP místo určení hodnot offsetu a načtení. To by nám však nedovolilo udělat další část.
Příklad 3 – Zobrazení Další 3 Výsledky
Teď pojďme zobrazit následující tři výsledky:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Takže jediná věc, kterou jsem změnil byl posun.
hodnoty offsetu a načítání mohou být také výrazem poskytovaným jako proměnná, parametr nebo konstantní skalární poddotaz. Při použití poddotazu nemůže odkazovat na žádné sloupce definované ve vnějším rozsahu dotazu (nemůže být korelován s vnějším dotazem).
následující příklady používají výrazy k zobrazení dvou přístupů k stránkování výsledků.
Příklad 4 – Stránkování pomocí Číslo Řádku
Tento příklad používá výrazy zadejte číslo řádku pro spuštění na.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Tady, jsem použil @StartRow int = 1 upřesnit, že výsledky by měly začít v první řadě.
zde je to, co se stane, když tuto hodnotu zvýším na 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
začíná na druhém řádku. Pomocí této metody mohu určit přesný řádek, na kterém se má začít.
Příklad 5 – Stránkování podle Čísla Stránky
Tento příklad je téměř totožný s předchozím příkladě, kromě toho, že umožňuje zadat číslo stránky, jako protiklad k řádku číslo.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Takže první výsledek je stejný. Nicméně, pojďme se podívat, co se stane, když přírůstek @PageNumber2 (přejmenoval jsem tuto proměnnou tak, aby odrážely jeho nový účel).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
tentokrát výsledky začínají na čtvrtém řádku. Takže pomocí této metody můžete jednoduše předat číslo stránky spíše než číslo řádku.
Příklad 6 – Stránkování Smyčky
Chcete-li dokončit, zde je rychlý příklad, který prochází všechny stránky a určuje počáteční číslo řádku pro každou iteraci:
DECLARE @StartRow int = 1, @RowsPerPage int = 3;WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow BEGIN SELECT * FROM Genres ORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;SET @StartRow = @StartRow + @RowsPerPage; CONTINUEEND;
Výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 7 | Rap || 8 | Punk |+-----------+---------+(2 rows affected)
Příklad 7 – ŘÁDEK vs ŘÁDKY
Pokud narazíte na kód, který používá ROW místo ROWS oba argumenty dělat to samé. Jsou synonyma a jsou poskytovány pro kompatibilitu ANSI.
zde je první příklad na této stránce, ale s ROW namísto ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Příklad 8 – PRVNÍ vs DALŠÍ
totéž platí pro FIRSTNEXT. Jedná se o synonyma poskytovaná pro kompatibilitu ANSI.
zde je předchozí příklad, ale s FIRST namísto NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
výsledek:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+