záradékkal ebben a cikkben az OFFSET és a FETCH záradékokat vizsgáljuk. OFFSET és FETCH együtt használják a SELECT utasítás ORDER BY záradék, hogy egy eszközt letölteni egy sor rekordot. A visszatéréshez szükséges kezdő sort az eltolás értéke határozza meg, és az attól a ponttól a lekéréssel visszatérendő sorok maximális száma.
a lecke összes példája a Microsoft SQL Server Management Studio és az AdventureWorks2012 adatbázison alapul. Ezeket az ingyenes eszközöket az útmutatóm segítségével kezdheti el használni az SQL Server használatának megkezdése.
az OFFSET és FETCH használata az ORDER BY záradékkal
Az SQL utasításból visszatérő sorok lehetnek minden vagy semmi viszony. Sok esetben a visszaadott sorok száma nagyon nagy, és ez problémákat okozhat, ha csak az eredményhalmaz egy részére van szüksége.
Ha az eredményeket a sorrend szerint záradékkal rendezzük, akkor néhány lehetőség jön létre a visszaadott sorok számának korlátozására:
- a TOP segítségével meghatározott számú sort adhat vissza.
- használhatja az OFFSET és a FETCH funkciót.
ebben a cikkben merülünk többet OFFSET és FETCH. Ha többet szeretne megtudni a TOP-ról, olvassa el az első lépések az SQL Server használatával című cikket: 2. Rendezze A Lekérdezés Eredményeit.
OFFSET
az OFFSET argumentum az eredményből származó sorok visszatérésének kiindulópontjának azonosítására szolgál. Az OFFESET-et argumentumnak nevezzük, mivel technikailag része a sorrend záradék. Az eltolás az átugrandó sorok száma, mielőtt beillesztené őket az eredménybe.
az OFFSET argumentum általános formája:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
ahol a kihagyandó sorok értéke nagyobb vagy egyenlő nullával.
például, hogy az összes, de az első 10 alkalmazottak által megrendelt HireDate írhatnál
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
Íme néhány dolog, hogy fontolja meg OFFSET
- OFFSET része a ORDER BY záradék. Önmagában nem használható.
- az eltolási értékeknek nullának vagy nagyobbnak kell lenniük. A negatív szám hibát eredményez.
- ha az eltolás 0, akkor nem kerül sor kihagyásra.
- Ha az eltolás nagyobb, mint a rendezett eredmények sorainak száma, akkor a rendszer nem ad vissza sorokat.
FETCH
a FETCH argumentum egy meghatározott számú sor visszaadására szolgál. A lekérés önmagában nem használható, az eltolással együtt használják.
folytatva a példánkat, megmutathatjuk a 11.-15. alkalmazottakat, akiket ezzel a kijelentéssel foglalkoztatunk
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
az alábbi ábrán látható az eltolás és a Lekérés a munkahelyen. Az OFFSET az első 10 sor kihagyására szolgál, a FETCH pedig a következő 5 megjelenítésére szolgál.
az OFFSET és a FETCH kombinációja megkönnyíti a sorok “csúszó” ablakának lekérését. Az ablak kezdetét az eltolás, a magasságot pedig a lekérés határozza meg.
az OFFSET és a FETCH használata
Lapozás
az OFFSET és a FETCH egyik legnépszerűbb felhasználási módja a lapozás. Nem kétséges, hogy meglátogatta a webhelyet, ahol az elemek listáját látja, alul pedig az oldalszámok listája vagy a következő gomb található.
folyamatosan használjuk a lapozást az interneten. A legnépszerűbb példa, amire gondolok, a Google:
a Google alatti számok az oldalszámokat képviselik. A keresési eredmények egy része minden kattintással visszatér.
hasonló módon tegyük fel, hogy van egy weboldalunk, amely a HireDate alkalmazottait jeleníti meg. Ha 20 alkalmazottat szeretnénk megjeleníteni egy oldalon, és a 3. oldalt (alkalmazottak 21-30) jelenítenénk meg, akkor a következő lekérdezést használhatnánk:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Ez a lekérdezés arra utasítja az SQL-t, hogy:
- válassza ki az alkalmazottak adatait
- Rendelje meg az információkat HireDate szerint
- hagyja ki a 20 sort, és indítsa el az eredmények megjelenítését a 21.
- megjeleníti a következő 10 találati Sort.
Top rekordok megszerzése
Ha a lekérdezés első tíz sorát szeretné megkapni, akkor ezt megteheti az eltolás 0 értékre állításával. Ne feledje, hogy az eltolás meghatározza a kihagyandó sorok számát. Ha nullára állítjuk, azt mondjuk az SQL-nek, hogy az első sorból induljon.
az eltolás beállítása után a következő feladat a lekérés beállítása. Mivel az első tízet keressük, a 10 mellé állítjuk a FETCH – et.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Ha ismeri a felső záradékot, észrevehette, hogy hasonlóságok vannak a két módszer között. Például a fenti példában a TOP használata úgy néz ki, mint
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
vannak olyan szempontok a tetejére, amelyek nem vonatkoznak az eltolásra és a lekérésre; például engedélyezettek az utasításokban megrendelés nélkül, de mint látható ebben a példában, egyenértékű eredményeket adnak vissza.
alsó rekordok beszerzése
az eredményhalmaz alsó rekordjainak megszerzéséhez kétféleképpen lehet. Az első az, hogy az eredményt csökkenő sorrendben rendezzük, szemben a növekvő sorrenddel. Ez az alsó eredményeket a tetejére helyezi. Ezután használhatja a fetch-et a szokásos módon.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Ha nem szeretné megváltoztatni az eredmények sorrendjét, akkor egy al lekérdezéssel megkaphatja a rekordok számát. Ezt követően fel lehet használni az eltolás megfelelő beállításához.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
ebben a módszerben bizonyos veszély áll fenn, mivel az eltolás kiszámításához használt kifejezés nullánál kisebb értéket eredményezhet. Példánkban ez akkor fordulhat elő, ha a sorok teljes száma kevesebb, mint tíz. Ennek a feltételnek a védelme érdekében szeretne beépíteni egy esettanulmányt a logikájába, hogy ellenőrizze ezt a feltételt.
mintavételi adatok az eredményhalmaz közepén
a FETCH and OFFSET egyik szép tulajdonsága, hogy SQL-t írhat az eredmény közepétől származó mintavételre vagy tesztelésre. Ez egy nagyon hasznos, ha látnia kell egy pillantást az eredménykészlet közepére, talán több millió sort tartalmaz, anélkül, hogy az összes sort megjelenítené a felülvizsgálni kívánt pontig.
a középső mintavételhez ugyanazt a logikát használná, mint a lapozáshoz. Természetesen a beolvasott sorok száma ebben az esetben sokkal nagyobb lehet.
az offset és FETCH
ofszet és FETCH hatása csak az ORDER BY záradékkal együtt működik. Valójában az SQL először lekéri a megadott adatokat, például oszlopokat, majd növekvő vagy csökkenő sorrendben rendezi az adatokat.
csak a lépés befejezése után kerülnek kihagyásra a sorok és az eredmények.
határesetek
mivel egyes értékek hibát eredményezhetnek, például negatív eltolást, vizsgáljuk meg az értékek különböző kombinációit, amelyek meghaladhatják a táblázat sorainak számát, hogy megértsük, mely értékek használata biztonságos, és mely értékek okoznak SQL hibát.
példáinkat az emberi erőforrásokra alapozzuk.Munkavállalói táblázat, amely 290 sort tartalmaz.
az egyetlen hiba, ha az OFFSET negatív. Vannak olyan esetek, amelyek nem adnak vissza sorokat, vagy kevesebb Sort adhatnak vissza, mint gondolná, de ezek az esetek nem dobnak hibákat. A legtöbb esetben ezek a helyzetek akkor fordulnak elő, amikor vagy az eltolás értéke nagyobb, mint az eredmény sorainak száma (az összes sor kihagyásra kerül), vagy megpróbálja lekérni az eredmény “múltját”, majd végét.