în acest articol vom explora clauzele OFFSET și FETCH. OFFSET și FETCH sunt utilizate împreună cu clauza SELECT order by pentru a oferi un mijloc de a prelua o serie de înregistrări. Rândul de pornire pentru a reveni este determinată de valoarea OFFSET și numărul maxim de rânduri pentru a reveni din acel moment de FETCH.
toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și baza de date AdventureWorks2012. Puteți începe să utilizați aceste instrumente gratuite folosind ghidul meu Noțiuni de bază folosind SQL Server.
utilizarea OFFSET și FETCH cu clauza ORDER BY
întoarcerea rândurilor dintr-o instrucțiune SQL poate fi o afacere totul sau nimic. În multe cazuri, numărul de rânduri returnate este foarte mare și acest lucru poate cauza probleme dacă aveți nevoie doar de o parte din setul de rezultate.
când rezultatele sunt sortate folosind clauza ORDER BY, atunci unele opțiuni intră în joc pentru a limita numărul de rânduri returnate:
- puteți utiliza TOP pentru a returna un număr specificat de rânduri.
- puteți utiliza OFFSET și FETCH.
în acest articol vom arunca cu capul în afla mai multe despre OFFSET și FETCH. Pentru a afla mai multe despre TOP, citiți articolul Noțiuni de bază cu SQL Server: 2. Sortați Rezultatele Interogării.
OFFSET
argumentul OFFSET este utilizat pentru a identifica punctul de plecare pentru a returna rândurile dintr-un rezultat. OFFESET se numește argument, deoarece face parte din punct de vedere tehnic din clauza ORDER BY. Offsetul este numărul de rânduri de omis înainte de a le include în rezultat.
forma generală pentru argumentul OFFSET este:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
unde rândurile de sărit sunt o valoare mai mare sau egală cu zero.
de exemplu, pentru a arăta toți, cu excepția primilor 10 angajați, ordonați de HireDate, puteți scrie
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
iată câteva lucruri de luat în considerare despre OFFSET
- OFFSET face parte din clauza ORDER BY. Nu poate fi folosit singur.
- valorile OFFSET trebuie să fie zero sau mai mare. Un număr negativ duce la o eroare.
- când OFFSET-ul este 0, atunci nu sunt omise rânduri.
- dacă OFFSET-ul este mai mare decât numărul de rânduri din rezultatele ordonate, atunci nu sunt returnate rânduri.
FETCH
argumentul FETCH este folosit pentru a returna un anumit număr de rânduri. FETCH nu poate fi folosit de la sine, este utilizat împreună cu OFFSET.
continuând cu exemplul nostru, putem arăta angajații 11 până la 15 angajați folosind această declarație
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
în diagrama următoare puteți vedea compensarea și preluarea la locul de muncă. OFFSET este utilizat pentru a sări peste primele 10 rânduri și FETCH este apoi utilizat pentru a afișa următoarele 5.
combinația de OFFSET și preluare facilitează recuperarea unei ferestre „glisante” de rânduri. Începutul ferestrei este determinată de OFFSET și înălțimea de FETCH.
utilizări pentru compensare și preluare
paginare
una dintre cele mai populare utilizări pentru compensare și preluare este paginarea. Fără îndoială că ați vizitat site-ul web unde vedeți o listă de articole și în partea de jos există o listă de numere de pagină sau un buton următor.
folosim paginarea tot timpul pe web. Cel mai popular exemplu la care mă pot gândi este Google:
numerele de mai jos Google reprezintă numere de pagină. O parte din rezultatele căutării sunt returnate cu fiecare clic.
în mod similar, să presupunem că avem o pagină web care afișează angajații prin HireDate. Dacă am dori să afișăm 20 de angajați pe o pagină și afișăm a 3-A pagină (angajați 21-30), am putea folosi următoarea interogare:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
această interogare instruiește SQL la:
- selectați informațiile angajat
- comanda informațiile de HireDate
- sări peste 20 de rânduri și începe rezultatele de afișare de la 21
- afișa următoarele 10 rânduri de rezultate.
obținerea înregistrărilor de Top
Dacă doriți să obțineți primele zece rânduri într-o interogare, atunci puteți face acest lucru setând OFFSET la 0. Amintiți-vă OFFSET specifică numărul de rânduri pentru a sări peste. Setându-l la zero, îi spunem lui SQL să înceapă de la primul rând.
odată ce OFFSET este setat, următoarea ordine de afaceri este de a seta FETCH. Din moment ce căutăm primii zece, am stabilit FETCH lângă 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
dacă sunteți familiarizat cu clauza de sus, este posibil să fi observat că există asemănări între aceste două metode. De exemplu, la exemplul de mai sus folosind TOP ar arata ca
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
există aspecte la început, care nu se aplică pentru a compensa și FETCH; cum ar fi fiind permise în declarații fără o comandă de, dar după cum puteți vedea pentru acest exemplu, se întorc rezultate echivalente.
obținerea înregistrărilor de Jos
pentru a obține înregistrările de jos într-un set de rezultate, există două moduri. Primul este de a ordona rezultatul în ordine descrescătoare, spre deosebire de ascendent. Aceasta este plasează rezultatele de jos în partea de sus. Apoi, puteți utiliza fetch ca de obicei.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
dacă nu doriți să modificați ordinea rezultatelor, puteți utiliza o sub-interogare pentru a obține numărul de înregistrări. Acest lucru poate fi apoi utilizat pentru a seta compensarea în consecință.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
există un anumit pericol în această metodă ca expresia pentru a calcula OFFSET poate duce la o valoare mai mică de zero. În exemplul nostru, acest lucru s-ar putea întâmpla dacă numărul total de rânduri ar fi mai mic de zece. Pentru a vă apăra împotriva acestei condiții, doriți să încorporați o declarație de caz în logica dvs. pentru a verifica această condiție.
eșantionarea datelor în mijlocul unui set de rezultate
o caracteristică frumoasă a preluării și compensării puteți scrie SQL pentru a proba sau testa datele din mijlocul rezultatului. Acesta este un foarte util, dacă aveți nevoie pentru a vedea să ia o privire la mijlocul unui set de rezultate, probabil, unul care conține milioane de rânduri, fără a afișa toate rândurile până la punctul pe care doriți să revizuiască.
pentru a proba în mijloc, ați folosi aceeași logică ca și pentru paginare. Desigur, numărul de rânduri pe care le aduceți în acest caz poate fi mult mai mare.
efectul comenzii de pe OFFSET și FETCH
OFFSET și FETCH funcționează numai împreună cu o clauză ORDER BY. De fapt, SQL preia mai întâi datele pe care le-ați specificat, cum ar fi coloanele, apoi ordonați datele în ordine crescătoare sau descrescătoare.
numai după ce acest pas a fost finalizat, rândurile sunt omise și rezultatele produse.
cazuri limită
deoarece unele valori pot duce la o eroare, cum ar fi o compensare negativă, să explorăm diferite combinații de valori care pot depăși numărul de rânduri dintr-un tabel pentru a înțelege ce valori sunt sigure de utilizat și care valori ar arunca o eroare SQL.
ne vom baza exemplele pe resursele umane.Tabelul angajaților, care conține 290 de rânduri.
singurul caz care are ca rezultat o eroare este atunci când compensarea este negativă. Există cazuri care nu returnează rânduri sau pot returna mai puține rânduri decât credeți, dar aceste cazuri nu aruncă erori. În cea mai mare parte, aceste situații apar atunci când fie valoarea OFFSET este mai mare decât numărul de rânduri din rezultat (toate rândurile sunt omise), fie încercați să preluați „trecut”, apoi sfârșitul rezultatului.