tässä artikkelissa tarkastelemme OFFSET ja nouda lausekkeita. OFFSET ja FETCH käytetään yhdessä SELECT statement ORDER by lauseke tarjota keino hakea erilaisia tietueita. Palautettava alkurivi määräytyy SIIRTOARVON ja palautettavien rivien enimmäismäärän mukaan noudon avulla.
Kaikki tämän oppitunnin esimerkit perustuvat Microsoft SQL Server Management studioon ja AdventureWorks2012-tietokantaan. Voit aloittaa käyttämällä näitä ilmaisia työkaluja käyttämällä My Guide aloittaminen SQL Server.
OFFSET-ja FETCH-komennon käyttäminen lausekkeella
rivien palauttaminen SQL-lausekkeesta voi olla kaikki tai ei mitään-tapaus. Monissa tapauksissa palautettujen rivien määrä on hyvin suuri ja tämä voi aiheuttaa ongelmia, jos sinun tarvitsee vain osa tulosjoukosta.
kun tulokset on lajiteltu lausekkeittain järjestykseen, tulee käyttöön joitakin vaihtoehtoja, joilla rajoitetaan palautettujen rivien määrää:
- voit käyttää yläosaa palauttaaksesi tietyn rivimäärän.
- voit käyttää offsetia ja noutoa.
tässä artikkelissa sukelletaan oppimaan lisää offsetista ja FETCHISTÄ. Saadaksesi lisätietoja topista, lue artikkeli SQL Server: 2: n käytön aloittaminen. Lajittele Kyselyn Tulokset.
OFFSET
OFFSET-argumenttia käytetään osoittamaan tulosrivien paluupistettä. OFFESETIA kutsutaan argumentiksi, koska se on teknisesti osa järjestystä lausekkeittain. OFFSET on rivien määrä ohittaa ennen niiden sisällyttämistä tulokseen.
OFFSET-argumentin yleinen muoto on:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
, jossa rivien-to-skip on arvo, joka on suurempi tai yhtä suuri kuin nolla.
esimerkiksi Hiredaten tilaamien kaikkien paitsi 10 ensimmäisen työntekijän osoittamiseksi voisi kirjoittaa
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
Tässä muutamia huomioita offsetista
- OFFSET on osa järjestystä lausekkeittain. Sitä ei voi käyttää yksinään.
- OFFSET-arvojen on oltava nolla tai suurempi. Negatiivinen luku johtaa virheeseen.
- kun OFFSET on 0, niin rivejä ei ohiteta.
- Jos OFFSET on suurempi kuin järjestettyjen tulosten rivimäärä, rivejä ei palauteta.
nouda
nouto-argumenttia käytetään palauttamaan joukko rivejä. FETCHIÄ ei voi käyttää sellaisenaan, sitä käytetään OFFSET-toiminnon yhteydessä.
jatkaen esimerkkiämme, voimme näyttää 11.-15. palkatut työntekijät tällä lausekkeella
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
seuraavassa kaaviossa näet OFFSET-ja FETCH-työt. OFFSET käytetään ohittaa ensimmäiset 10 riviä ja FETCH käytetään näyttämään seuraavat 5.
OFFSET-ja FETCH-yhdistelmällä on helppo hakea ”liukuva” rivien ikkuna. Ikkunan alku määräytyy OFFSET-ja korkeus nouto.
OFFSET-ja NOUTOKÄYTTÖ
hakulaitteet
yksi suosituimmista OFFSET-ja NOUTOKÄYTTÖTAVOISTA on hakulaitteet. Epäilemättä olet käynyt verkkosivuilla, jossa näet luettelon kohteita ja alareunassa on luettelo sivunumerot tai Seuraava-painiketta.
käytämme hakulaitteita koko ajan verkossa. Suosituin esimerkki, jonka keksin, on Google:
Googlen alla olevat numerot edustavat sivunumeroita. Osa hakutuloksista palautetaan jokaisella klikkauksella.
vastaavasti oletetaan, että meillä on web-sivu, jossa työntekijät esitellään Hiredaten mukaan. Jos halusimme näyttää 20 työntekijää sivulla, ja näytimme 3. sivun (työntekijät 21-30), voisimme käyttää seuraavaa kyselyä:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Tämä kysely ohjeistaa SQL: ää:
- valitse työntekijätiedot
- tilaa tiedot HireDate
- Ohita 20 riviä ja aloita tulosten näyttäminen 21.päivältä
- Näytä seuraavat 10 tulosriviä.
Huippuennätysten saaminen
Jos haluat saada kyselyssä kymmenen parasta riviä, voit tehdä sen asettamalla OFFSET arvoon 0. Muista siirtymä määrittää ohitettavien rivien määrän. Asettamalla sen nollaan käskemme SQL: ää aloittamaan ensimmäisestä rivistä.
kun OFFSET on asetettu, seuraava käsittelyjärjestys on asettaa FETCH. Koska etsimme kärkikymmenikköä, asetamme Fetchin sijalle 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Jos tunnet ylimmän lausekkeen, olet ehkä huomannut näiden kahden menetelmän välillä olevan yhtäläisyyksiä. Esimerkiksi yllä olevaan esimerkkiin käyttäen TOP näyttäisi
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
on olemassa aspekteja, jotka eivät päde OFFSET ja FETCH; kuten sallitaan lauseissa ilman käskyä, mutta kuten näette tästä esimerkistä, ne palauttavat vastaavat tulokset.
Pohjaennätysten saaminen
pohjaennätysten saamiseksi tulosjoukossa on kaksi tapaa. Ensimmäinen on järjestää tulos alenevaan järjestykseen vastakohtana nousevalle. Tämä on paikka pohja tulokset alkuun. Sitten voit käyttää noutoa normaalisti.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Jos et halua muuttaa tulosten järjestystä, voit käyttää alikyselyä saadaksesi ennätysluvun. Tätä voidaan sitten käyttää offset vastaavasti.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
tässä menetelmässä on tietty vaara, sillä OFFSET-arvon laskemiseksi käytettävä lauseke voi johtaa arvoon, joka on pienempi kuin nolla. Meidän esimerkissämme näin voisi käydä, jos rivien kokonaismäärä olisi alle kymmenen. Puolustautua tätä ehtoa haluat sisällyttää TAPAUSILMOITUKSEN logiikkaa tarkistaa vastaan tämän ehdon.
Näytteenottotiedot kesken tulosjoukon
yksi kiva ominaisuus nouto-ja OFFSET voit kirjoittaa SQL: n näyte-tai testitietoihin tuloksen keskeltä. Tämä on todella kätevä, jos haluat nähdä kurkistaa keskellä tulosjoukon, ehkä yksi sisältää miljoonia rivejä, näyttämättä kaikki rivit jopa pisteeseen haluat tarkistaa.
kesken otokseen käyttäisit samaa logiikkaa kuin hakulaitteeseen. Tietenkin, rivien määrä voit hakea tässä tapauksessa voi olla paljon suurempi.
vaikutus ORDER BY OFFSET ja nouda
OFFSET ja nouda vain työ yhdessä järjestyksessä lauseke. Käytännössä SQL hakee ensin määrittämäsi tiedot, kuten sarakkeet, ja järjestää sitten tiedot nousevaan tai laskevaan järjestykseen.
vasta tämän vaiheen päätyttyä rivit ohitetaan ja tulokset tuotetaan.
Rajatapaukset
koska jotkut arvot voivat johtaa virheeseen, kuten negatiiviseen OFFSETIIN, tutkitaan erilaisia arvojen yhdistelmiä, jotka voivat ylittää taulukon rivien määrän ymmärtääkseen, mitkä arvot ovat turvallisia käyttää ja mitkä arvot heittäisivät SQL-virheen.
perustamme esimerkit inhimillisistä voimavaroista.Työntekijätaulukko, joka sisältää 290 riviä.
ainoa tapaus, joka johtaa virheeseen on, kun OFFSET on negatiivinen. On tapauksia, jotka eivät palaa rivejä, tai voi palauttaa vähemmän rivejä kuin luulet, mutta nämä tapaukset eivät heitä virheitä. Suurimmaksi osaksi, nämä tilanteet tapahtuvat, kun joko OFFSET arvo on suurempi kuin rivien määrä tuloksen (kaikki rivit ohitetaan), tai yrität hakea ”ohi” sitten loppuun tuloksen.