i denne artikel undersøger vi OFFSET og hente klausuler. OFFSET og FETCH bruges sammen med SELECT statement ORDER by-klausulen for at give et middel til at hente en række poster. Startrækken, der skal returneres, bestemmes af FORSKYDNINGSVÆRDIEN og det maksimale antal rækker, der skal returneres fra dette punkt ved hentning.
alle eksempler på denne lektion er baseret på Microsoft Server Management Studio og Adventurearbejds2012-databasen. Du kan komme i gang med at bruge disse gratis værktøjer ved hjælp af min Guide.
brug af OFFSET og hent med order BY-klausulen
returnering af rækker fra en KVL-erklæring kan være en alt eller intet affære. I mange tilfælde er antallet af returnerede rækker meget stort, og dette kan forårsage problemer, hvis du kun har brug for en del af resultatsættet.
når resultaterne sorteres ved hjælp af klausulen rækkefølge efter, kommer nogle indstillinger i spil for at begrænse antallet af returnerede rækker:
- du kan bruge TOP til at returnere et bestemt antal rækker.
- du kan bruge OFFSET og hente.
i denne artikel dykker vi ind i Lær mere om OFFSET og hent. Hvis du vil vide mere om TOP, skal du læse artiklen kom godt i gang med server: 2. Sorter Dine Forespørgselsresultater.
OFFSET
offset-argumentet bruges til at identificere udgangspunktet for at returnere rækker fra et resultat. OFFESET kaldes et argument, da det teknisk set er en del af Order by-klausulen. Forskydningen er antallet af rækker, der skal springes over, før de inkluderes i resultatet.
den generelle form for OFFSET-argumentet er:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
hvor rækkerne til Spring er en værdi større end eller lig med nul.
for eksempel for at vise alle undtagen de første 10 medarbejdere, bestilt af HireDate, kan du skrive
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
Her er nogle ting, du skal overveje om OFFSET
- OFFSET er en del af ordreafgørelsen. Det kan ikke bruges alene.OFFSETVÆRDIERNE skal være nul eller større. Et negativt tal resulterer i en fejl.
- når forskydning er 0, springes ingen rækker over.
- hvis forskydningen er større end antallet af rækker i de bestilte resultater, returneres der ingen rækker.
Hent
argumentet Hent bruges til at returnere et bestemt antal rækker. Hentning kan ikke bruges af sig selv, det bruges sammen med OFFSET.
fortsat med vores eksempel kan vi vise de 11.til 15. ansatte ansat ved hjælp af denne erklæring
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
i det følgende diagram kan du se forskydning og hente på arbejde. OFFSET bruges til at springe de første 10 rækker over, og hent bruges derefter til at vise de næste 5.
kombinationen af OFFSET og hente gør det nemt at hente et “glidende” vindue af rækker. Vinduets start bestemmes af forskydning og højden ved hentning.
anvendelser til forskydning og hent
Personsøgning
en af de mest populære anvendelser til forskydning og hentning er Personsøgning. Ingen tvivl om du har besøgt hjemmeside, hvor du ser en liste over emner og nederst er der en liste over sidetal eller en næste knap.
Vi bruger Personsøgning hele tiden på nettet. Det mest populære eksempel, Jeg kan tænke på, er Google:
tallene under Google repræsenterer sidetal. En del af søgeresultaterne returneres med hvert klik.
på lignende måde antager vi, at vi har en hjemmeside, der viser medarbejdere af HireDate. Hvis vi ønskede at vise 20 medarbejdere på en side, og vi viste 3. side (medarbejdere 21-30), kunne vi bruge følgende forespørgsel:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
denne forespørgsel instruerer os til:
- Vælg medarbejderoplysningerne
- Bestil oplysningerne efter Ansættelsesdato
- spring over 20 rækker og start Vis resultater fra den 21.
- Vis de næste 10 rækker med resultater.
Hent top Records
Hvis du ønsker at få de ti øverste rækker i en forespørgsel, kan du gøre det ved at indstille OFFSET til 0. Husk forskydningen angiver antallet af rækker, der skal springes over. Ved at sætte det til nul, fortæller vi os at starte i første række.
når OFFSET er indstillet, er den næste forretningsorden at indstille hentning. Da vi leder efter top ti, sætter vi FETCH ved siden af 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Hvis du er bekendt med den øverste klausul, har du måske bemærket, at der er ligheder mellem disse to metoder. For eksempel til ovenstående eksempel ved hjælp af TOP ville se ud
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
Der er aspekter til toppen, der ikke gælder for OFFSET og hente; såsom at være tilladt i udsagn uden en ordre af, men som du kan se for dette eksempel, returnerer de tilsvarende resultater.
Hent nederste poster
for at få de nederste poster i et resultatsæt er der to måder. Den første er at bestille resultatet i faldende rækkefølge i modsætning til stigende. Dette placerer de nederste resultater til toppen. Derefter kan du bruge Hent som normalt.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Hvis du ikke ønsker at ændre rækkefølgen af resultaterne, kan du bruge en underforespørgsel til at få rekordtællingen. Dette kan derefter bruges til at indstille forskydningen i overensstemmelse hermed.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
Der er en vis fare i denne metode, da udtrykket til beregning af forskydning kan resultere i en værdi mindre end nul. I vores eksempel kunne dette ske, hvis det samlede antal rækker var mindre end ti. For at forsvare sig mod denne betingelse vil du gerne indarbejde en SAGSERKLÆRING i din logik for at kontrollere denne betingelse.
Sampling Data i midten af et resultat sæt
en dejlig funktion af hente og OFFSET du er i stand til at skrive til prøve eller test data fra midten af resultatet. Dette er en virkelig praktisk, hvis du har brug for at se kig på midten af et resultatsæt, måske en, der indeholder millioner af rækker, uden at vise alle rækkerne op til det punkt, du ønsker at gennemgå.
for at prøve i midten ville du bruge den samme logik som du ville til personsøgning. Selvfølgelig kan antallet af rækker, du henter i dette tilfælde, være meget større.
effekt af ORDER BY on OFFSET og FETCH
OFFSET og FETCH fungerer kun sammen med en ORDER BY-klausul. Kolonner, og bestil derefter dataene i stigende eller faldende rækkefølge.
først efter at dette trin er afsluttet, springes rækker over, og resultaterne produceres.
Boundary Cases
da nogle værdier kan resultere i en fejl, såsom en negativ forskydning, lad os undersøge forskellige kombinationer af værdier, der kan overstige antallet af rækker i en tabel for at forstå, hvilke værdier der er sikre at bruge, og hvilke værdier der vil kaste en KVM-fejl.
Vi baserer vores eksempler på Mennesketressourcer.Medarbejderbord, der indeholder 290 rækker.
det eneste tilfælde, der resulterer i en fejl, er, når forskydningen er negativ. Der er tilfælde, der ikke returnerer rækker, eller kan returnere færre rækker, end du tror, men disse sager kaster ikke fejl. For det meste opstår disse situationer, når enten OFFSETVÆRDIEN er større end antallet af rækker i resultatet (alle rækker springes over), eller du forsøger at hente “fortid” og derefter slutningen af resultatet.