v tomto článku prozkoumáme klauzule OFFSET a FETCH. OFFSET a FETCH se používají ve spojení s příkazem SELECT pořadí podle klauzule, aby poskytly prostředky k načtení řady záznamů. Počáteční řádek, který se má vrátit, je určen hodnotou offsetu a maximálním počtem řádků, které se od tohoto bodu vrátí pomocí FETCH.
všechny příklady pro tuto lekci jsou založeny na Microsoft SQL Server Management Studio a databáze AdventureWorks2012. Můžete začít používat tyto bezplatné nástroje pomocí mého průvodce Začínáme používat SQL Server.
použití offsetu a načtení s klauzulí ORDER BY
vrácení řádků z příkazu SQL může být záležitostí všeho nebo ničeho. V mnoha případech je počet vrácených řádků velmi velký a to může způsobit problémy, pokud potřebujete pouze část sady výsledků.
když jsou výsledky seřazeny podle klauzule pořadí podle, pak některé možnosti vstupují do hry omezit počet řádků vrácených:
- můžete použít TOP vrátit zadaný počet řádků.
- můžete použít OFFSET a FETCH.
v tomto článku se ponoříme do další informace o OFFSET a FETCH. Chcete-li se dozvědět více o TOP, přečtěte si článek Začínáme s SQL Server: 2. Seřadit Výsledky Dotazu.
OFFSET
argument OFFSET se používá k identifikaci výchozího bodu pro návrat řádků z výsledku. OFFESET se nazývá argument, protože je technicky součástí objednávky podle klauzule. Posun je počet řádků, které je třeba přeskočit, než je zahrnete do výsledku.
obecný formulář pro offsetový argument je:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
kde řádky-to-skip je hodnota větší nebo rovna nule.
například, ukázat všechny, ale prvních 10 zaměstnanců, nařídil HireDate můžete napsat
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
Zde jsou některé věci, aby zvážila o POSUN,
- ODSAZENÍ je součástí klauzule ORDER BY. Nemůže být použit samostatně.
- hodnoty offsetu musí být nulové nebo vyšší. Záporné číslo má za následek chybu.
- pokud je OFFSET 0, Žádné řádky nejsou přeskočeny.
- pokud je OFFSET větší než počet řádků v uspořádaných výsledcích, žádné řádky se nevracejí.
načíst
parametr načíst se používá k vrácení nastaveného počtu řádků. FETCH nemůže být použit sám o sobě, používá se ve spojení s OFFSET.
Pokračování našeho příkladu můžeme ukázat, 11. až 15 zaměstnanců pomocí tohoto prohlášení,
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
V následujícím diagramu můžete vidět, OFFSET a FETCH v práci. OFFSET se používá k přeskočení prvních 10 řádků a FETCH se pak používá k zobrazení dalších 5.
kombinace OFFSET a FETCH, aby bylo snadné získat „posuvné“ okno řádků. Začátek okna je určen posunem a výškou načtení.
používá pro OFFSET a FETCH
stránkování
jedním z nejpopulárnějších použití pro OFFSET a FETCH je stránkování. Není pochyb o tom, že jste navštívili web, kde vidíte seznam položek a ve spodní části je seznam čísel stránek nebo tlačítko Další.
stránkování používáme po celou dobu na webu. Nejvíce populární příklad mě napadá je Google:
čísla níže, Google představují čísla stránek. Část výsledků vyhledávání se vrací s každým kliknutím.
podobným způsobem předpokládejme, že máme webovou stránku zobrazující zaměstnance podle HireDate. Pokud bychom chtěli zobrazit 20 zaměstnanců na stránce, a my jsme byli zobrazování 3 strana (zaměstnanci 21-30), můžeme použít následující dotaz:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Tento dotaz pověřuje SQL:
- Vyberte zaměstnance, informace
- Objednávky informace o HireDate
- Přeskočit 20 řádků a začít zobrazovat výsledky z 21.
- Zobrazit další 10 řádků výsledků.
získání nejlepších záznamů
Pokud chcete získat prvních deset řádků v dotazu, můžete tak učinit nastavením offsetu na 0. Pamatujte, že posun určuje počet řádků, které chcete přeskočit. Nastavením na nulu říkáme SQL, aby začal v prvním řádku.
jakmile je nastaven OFFSET, další pořadí podnikání je nastavit FETCH. Vzhledem k tomu, že hledáme první desítku, nastavili jsme FETCH vedle 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Pokud jste obeznámeni s HORNÍM bodě možná jste si všimli, existují podobnosti mezi těmito dvěma metodami. Například výše uvedený příklad s použitím TOP bude vypadat
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
Tam jsou aspekty, na VRCHOLU, že to neplatí pro OFFSET a FETCH; jako je povolena v prohlášení bez příkazu, ale jak můžete vidět na tomto příkladu, se vrátí stejné výsledky.
získání spodních záznamů
Chcete-li získat dolní záznamy v sadě výsledků, existují dva způsoby. Prvním je pořadí výsledku v sestupném pořadí na rozdíl od vzestupu. To je umístí spodní výsledky na vrchol. Pak můžete použít fetch jako obvykle.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Pokud nechcete změnit pořadí výsledků, můžete použít sub dotaz získat počet záznamů. To pak může být použito pro odpovídající nastavení offsetu.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
v této metodě existuje určité nebezpečí, protože výraz pro výpočet offsetu může mít za následek hodnotu menší než nula. V našem příkladu by se to mohlo stát, pokud by celkový počet řádků byl menší než deset. Chcete-li se bránit proti této podmínce, chtěli byste do své logiky začlenit prohlášení o případu, abyste tuto podmínku zkontrolovali.
vzorkovací Data uprostřed sady výsledků
jedna pěkná funkce načtení a posunutí můžete psát SQL na vzorkování nebo testování dat od středu výsledku. To je opravdu užitečné, pokud potřebujete vidět, podívat se na středu sadu výsledků, možná jeden obsahující miliony řádků, bez zobrazení všechny řádky až do bodu, který chcete zkontrolovat.
Chcete-li vzorkovat uprostřed, použijte stejnou logiku jako pro stránkování. Počet řádků, které v tomto případě načtete, může být samozřejmě mnohem větší.
efekt ORDER BY on OFFSET a FETCH
OFFSET a FETCH fungují pouze ve spojení s klauzulí ORDER BY. Ve skutečnosti SQL nejprve načte zadaná data, například sloupce, a poté je uspořádá ve vzestupném nebo sestupném pořadí.
teprve po dokončení tohoto kroku jsou řádky přeskočeny a výsledky vytvořeny.
Hraniční Případy
Protože některé hodnoty mohou mít za následek chybu, jako negativní POSUN, pojďme prozkoumat různé kombinace hodnot, které může být vyšší než počet řádků v tabulce, pochopit hodnoty, které jsou bezpečné pro použití, a které hodnoty by se hodit SQL chyba.
založíme naše příklady z Lidskýchzdroje.Tabulka zaměstnanců, která obsahuje 290 řádků.
pouze v případě, že se výsledky v chybě je, pokud OFFSET je záporné. Existují případy, které nevracejí řádky nebo mohou vrátit méně řádků, než si myslíte, ale tyto případy nevyhodí chyby. Tyto situace se většinou vyskytují, když je buď hodnota offsetu větší než počet řádků ve výsledku (všechny řádky jsou přeskočeny), nebo se pokoušíte načíst „minulost“ a pak konec výsledku.