i denne artikkelen undersøker VI OFFSET og HENTE klausuler. OFFSET OG FETCH brukes sammen MED select statement ORDER BY-setningsdelen for å gi en måte å hente en rekke poster. Startraden som skal returneres, bestemmes av FORSKYVNINGSVERDIEN og det maksimale antall rader som skal returneres fra det punktet ved HENTING.

alle eksemplene for denne leksjonen er Basert På Microsoft SQL Server Management Studio og AdventureWorks2012-databasen. Du kan komme i gang med DISSE gratis verktøyene ved hjelp av Min Guide Komme I GANG MED SQL Server.

Bruke OFFSET og HENTE MED ORDER BY-klausulen

Retur rader fra EN SQL-setning kan være en alt eller ingenting affære. I mange tilfeller er antall rader som returneres svært store, og dette kan føre til problemer hvis du bare trenger en del av resultatsettet.

når resultatene er sortert VED HJELP AV ORDER BY-klausulen, så noen alternativer kommer inn i bildet for å begrense antall rader returnert:

1. DU kan bruke TOP for å returnere et bestemt antall rader.
2. du kan BRUKE OFFSET og HENTE.

i denne artikkelen dykker vi inn i lære mer OM OFFSET og HENTE. Hvis DU vil vite MER OM TOP, kan du lese artikkelen Komme i Gang med SQL Server: 2. Sortere Spørringsresultatene.

FORSKYVNING

FORSKYVNINGSARGUMENTET brukes til å identifisere startpunktet for å returnere rader fra et resultat. OFFESET kalles et argument siden det er teknisk en del AV ORDER BY-klausulen. FORSKYVNINGEN er antall rader som skal hoppes over før de tas med i resultatet.

det generelle skjemaet for FORSKYVNINGSARGUMENTET er:

SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS

hvor rader-til-hopp er en verdi større enn eller lik null.

for eksempel, for å vise alle, men de første 10 ansatte, bestilt Av HireDate, kan du skrive

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS

HER er noen ting du bør vurdere OM OFFSET

• OFFSET er en del AV ORDER BY-klausulen. Den kan ikke brukes av seg selv.
• OFFSET verdier må være null eller høyere. Et negativt tall resulterer i en feil.
• NÅR OFFSET er 0, blir ingen rader hoppet over.
• HVIS FORSKYVNING er større enn antall rader i de bestilte resultatene, returneres ingen rader.

HENT

HENT-argumentet brukes til å returnere et angitt antall rader. HENT kan ikke brukes av seg selv, den brukes sammen med OFFSET.

Fortsetter med vårt eksempel, kan vi vise 11 til 15 ansatte ansatt ved hjelp av denne setningen

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

i følgende diagram kan du se OFFSET og HENTE på jobb. OFFSET brukes til å hoppe over de første 10 radene og HENTE brukes deretter til å vise de neste 5.

kombinasjonen AV OFFSET og HENTE gjør det enkelt å hente et «glidende» vindu med rader. Starten av vinduet bestemmes AV OFFSET og høyden VED HENTING.

Bruker FOR OFFSET og HENTE

Paging

en av de mest populære bruker FOR OFFSET og HENTE er paging. Ingen tvil om du har besøkt nettsted der du ser en liste over elementer og nederst er det en liste over sidetall eller en neste knapp.

vi bruker personsøk hele tiden på nettet. Det mest populære eksemplet jeg kan tenke På Er Google:

tallene under Google representerer sidetall. En del av søkeresultatene returneres med hvert klikk.

på lignende måte, anta at vi har en nettside som viser ansatte Ved HireDate. Hvis vi ønsket å vise 20 ansatte på en side, og vi viste 3. side (ansatte 21-30), kunne vi bruke følgende spørring:

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

denne spørringen instruerer SQL til:

1. Velg ansattinformasjonen
2. Bestill informasjonen ved HireDate
3. Hopp over 20 rader og start vis resultater fra den 21.
4. Vis de neste 10 radene med resultater.

Få Topprekorder

hvis du ønsker å få de ti øverste radene i en spørring, kan DU gjøre det ved å sette OFFSET til 0. Husk AT FORSKYVNINGEN angir antall rader som skal hoppes over. Ved å sette den til null, forteller VI SQL å starte på første rad.

når OFFSET er satt, er neste rekkefølge å sette FETCH. Siden vi leter etter topp ti, setter VI FETCH VED siden av 10.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

hvis DU er kjent MED TOPPKLAUSULEN, har du kanskje lagt merke til at det er likheter mellom disse to metodene. For eksempel til ovenfor eksempel VED HJELP AV TOP ville se ut

SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate

det er aspekter TIL TOPPEN som ikke gjelder FOR OFFSET og HENTE; slik som å være tillatt i uttalelser UTEN EN ORDRE AV, men som du kan se for dette eksemplet, returnerer de tilsvarende resultater.

Få Bunnposter

for å få bunnposter i et resultatsett er det to måter. Den første er å bestille resultatet i synkende rekkefølge i motsetning til stigende. Dette plasserer bunnresultatene til toppen. Deretter kan du bruke fetch som normalt.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

hvis du ikke ønsker å endre rekkefølgen på resultatene, kan du bruke en sub spørring for å få posten teller. Dette kan da brukes til å sette forskyvningen tilsvarende.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY

det er en viss fare i denne metoden som uttrykket for å beregne OFFSET kan resultere i en verdi mindre enn null. I vårt eksempel kan dette skje hvis det totale antall rader var mindre enn ti. For å forsvare seg mot denne tilstanden vil du innlemme EN SAK uttalelse i din logikk for å sjekke mot denne tilstanden.

Sampling Data i midten av et resultatsett

en fin funksjon AV HENTE og OFFSET du kan skrive SQL for å prøve eller teste data fra midten av resultatet. Dette er en veldig praktisk hvis du trenger å se ta en titt på midten av et resultatsett, kanskje en som inneholder millioner av rader, uten å vise alle radene opp til det punktet du ønsker å se gjennom.

for å prøve i midten vil du bruke samme logikk som du ville for personsøking. Selvfølgelig kan antall rader du henter i dette tilfellet være mye større.

EFFEKT AV ORDER BY PÅ OFFSET og HENTE

OFFSET og HENTE bare arbeid i forbindelse MED EN ORDER BY-klausul. SQL henter faktisk først dataene du har angitt, for eksempel kolonner, og bestiller deretter dataene i stigende eller synkende rekkefølge.

først etter at dette trinnet er fullført, hoppes radene over og resultatene blir produsert.

Grense Tilfeller

siden noen verdier kan resultere i en feil, for eksempel en negativ FORSKYVNING, la oss utforske ulike kombinasjoner av verdier som kan overstige antall rader i en tabell for å forstå hvilke verdier som er trygge å bruke, og hvilke verdier ville kaste EN SQL-feil.

vi baserer våre eksempler på HumanResources.Ansattes tabell, som inneholder 290 rader.

DET eneste tilfellet som resulterer i en feil er når FORSKYVNINGEN er negativ. Det er tilfeller som ikke returnerer rader, eller kan returnere færre rader enn du tror, men disse tilfellene kaster ikke feil. For det meste oppstår disse situasjonene når ENTEN FORSKYVNINGSVERDIEN er større enn antall rader i resultatet (alle radene hoppes over), eller du prøver å hente «forbi» og deretter slutten av resultatet.