in dit artikel verkennen we de OFFSET en FETCH clausules. OFFSET en FETCH worden gebruikt in combinatie met de SELECT statement ORDER BY-clausule om een middel te bieden om een reeks records op te halen. De startrij om te retourneren wordt bepaald door de OFFSET waarde en het maximum aantal rijen om vanaf dat punt terug te keren door FETCH.
alle voorbeelden voor deze les zijn gebaseerd op Microsoft SQL Server Management Studio en de AdventureWorks2012 database. U kunt aan de slag met behulp van deze gratis tools met behulp van mijn gids aan de slag met behulp van SQL Server.
het gebruik van OFFSET en FETCH met de ORDER BY-clausule
het retourneren van rijen uit een SQL-statement kan een alles-of niets-affaire zijn. In veel gevallen is het aantal geretourneerde rijen erg groot en dit kan problemen veroorzaken als u slechts een deel van de resultaatset nodig hebt.
wanneer resultaten gesorteerd worden met de volgorde op clausule, dan zijn er enkele opties die het aantal geretourneerde rijen beperken:
- u kunt TOP gebruiken om een bepaald aantal rijen te retourneren.
- u kunt OFFSET en FETCH gebruiken.
In dit artikel duiken we in meer informatie over OFFSET en FETCH. Lees voor meer informatie over TOP het artikel aan de slag met SQL Server: 2. Sorteer Uw Zoekresultaten.
OFFSET
het OFFSET-argument wordt gebruikt om het beginpunt te identificeren om rijen uit een resultaat te retourneren. OFFESET wordt een argument genoemd omdat het technisch gezien deel uitmaakt van de ORDER BY-clausule. De verschuiving is het aantal rijen dat moet worden overgeslagen voordat u ze in het resultaat opneemt.
de algemene vorm voor het OFFSET argument is:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
waarbij de rows-to-skip is een waarde groter dan of gelijk aan nul.
bijvoorbeeld, om alle behalve de eerste 10 werknemers te tonen, geordend door HireDate, zou je
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
Hier zijn enkele dingen die je moet overwegen over OFFSET
- OFFSET is onderdeel van de ORDER BY-clausule. Het kan niet alleen worden gebruikt.
- OFFSET waarden moeten nul of hoger zijn. Een negatief getal resulteert in een fout.
- wanneer verschuiving 0 is, worden geen rijen overgeslagen.
- als de verschuiving groter is dan het aantal rijen in de geordende resultaten, worden geen rijen geretourneerd.
FETCH
het FETCH-argument wordt gebruikt om een bepaald aantal rijen terug te geven. FETCH kan niet op zichzelf worden gebruikt, het wordt gebruikt in combinatie met OFFSET.
doorgaan met ons voorbeeld, We kunnen de 11e tot en met 15de werknemers laten zien die zijn ingehuurd met behulp van deze instructie
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
In het volgende diagram kunt u OFFSET en FETCH zien op het werk. OFFSET wordt gebruikt om de eerste 10 rijen over te slaan en FETCH wordt vervolgens gebruikt om de volgende 5 weer te geven.
De combinatie van OFFSET en FETCH maakt het gemakkelijk om een” schuifvenster ” van rijen op te halen. Het begin van het venster wordt bepaald door OFFSET en de hoogte door FETCH.
gebruikt voor OFFSET en FETCH
Paging
een van de meest populaire toepassingen voor OFFSET en FETCH is paging. Ongetwijfeld heb je een website bezocht waar je een lijst met items ziet en onderaan staat een lijst met paginanummers of een knop Volgende.
we gebruiken paging de hele tijd op het web. Het meest populaire voorbeeld dat ik kan bedenken is Google:
De getallen onder Google vertegenwoordigen paginanummers. Bij elke klik wordt een deel van de zoekresultaten geretourneerd.
op soortgelijke wijze, stel dat we een webpagina hebben met werknemers door HireDate. Als we wilden 20 werknemers weer te geven op een pagina, en we waren het weergeven van de 3e pagina (werknemers 21-30), konden we de volgende query gebruiken:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
deze query instrueert SQL om:
- selecteer de werknemersinformatie
- Bestel de informatie door HireDate
- 20 rijen overslaan en resultaten van de 21e
- weergeven van de volgende 10 rijen met resultaten.
Toprecords ophalen
Als u de top tien rijen in een query wilt krijgen, kunt u dit doen door OFFSET in te stellen op 0. Onthoud de verschuiving geeft het aantal rijen aan dat moet worden overgeslagen. Door het in te stellen op nul, vertellen we SQL om te beginnen bij de eerste rij.
zodra OFFSET is ingesteld, is de volgende volgorde om FETCH in te stellen. Omdat we op zoek zijn naar de top tien, zetten we APPETCH op 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Als u bekend bent met de top-clausule kunt u gemerkt hebben dat er overeenkomsten zijn tussen deze twee methoden. In het bovenstaande voorbeeld zou het gebruik van TOP er bijvoorbeeld uitzien als
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
Er zijn aspecten aan TOP die niet van toepassing zijn op OFFSET en FETCH; zoals toegestaan zijn in statements zonder een ORDER van, maar zoals je kunt zien voor dit voorbeeld, geven ze gelijkwaardige resultaten.
onderste Records ophalen
om de onderste records in een resultaatset te krijgen zijn er twee manieren. De eerste is om het resultaat in dalende volgorde te rangschikken in tegenstelling tot ascending. Dit plaatst de onderste resultaten naar de top. Dan kun je fetch gebruiken als normaal.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Als u de volgorde van de resultaten niet wilt wijzigen, kunt u een subquery gebruiken om het aantal records te verkrijgen. Dit kan dan worden gebruikt om de offset dienovereenkomstig in te stellen.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
Er is een zeker gevaar in deze methode omdat de uitdrukking om OFFSET te berekenen kan resulteren in een waarde kleiner dan nul. In ons voorbeeld zou dit kunnen gebeuren als het totale aantal rijen minder dan tien was. Om je tegen deze voorwaarde te verdedigen zou je een CASE statement willen opnemen in je logica om tegen deze voorwaarde te controleren.
Sampling Data in het midden van een resultaatset
een leuke functie van FETCH en OFFSET je kunt SQL schrijven om data te sampleen of te testen vanuit het midden van het resultaat. Dit is echt handig als u wilt zien neem een kijkje in het midden van een resultaatset, misschien een die miljoenen rijen bevat, zonder alle rijen weer te geven tot het punt dat u wilt bekijken.
om een sample in het midden te maken zou je dezelfde logica gebruiken als voor paging. Natuurlijk kan het aantal rijen dat u in dit geval ophaalt veel groter zijn.
Effect van ORDER BY on OFFSET en FETCH
OFFSET en FETCH werken alleen samen met een ORDER BY-clausule. In feite haalt SQL eerst de gegevens op die u hebt opgegeven, zoals kolommen, en ordent de gegevens vervolgens in oplopende of aflopende volgorde.
pas nadat deze stap is voltooid, worden rijen overgeslagen en worden de resultaten geproduceerd.
Boundary Cases
aangezien sommige waarden kunnen resulteren in een fout, zoals een negatieve verschuiving, laten we verschillende combinaties van waarden verkennen die het aantal rijen in een tabel kunnen overschrijden om te begrijpen welke waarden veilig zijn om te gebruiken, en welke waarden een SQL-fout zouden geven.
we baseren onze voorbeelden op de HumanResources.Employee table, die 290 rijen bevat.
het enige geval dat resulteert in een fout is wanneer de OFFSET negatief is. Er zijn gevallen waarin geen rijen worden geretourneerd of minder rijen worden geretourneerd dan u denkt, maar die gevallen gooien geen fouten. Voor het grootste deel komen deze situaties voor wanneer ofwel de OFFSET waarde groter is dan het aantal rijen in het resultaat (alle rijen worden overgeslagen), of u probeert “verleden” dan einde van het resultaat op te halen.