Använda OFFSET och hämta med ORDER BY-klausulen

i den här artikeln utforskar vi offset-och FETCH-klausulerna. OFFSET och FETCH används tillsammans med select-satsen ORDER BY-klausul för att tillhandahålla ett sätt att hämta en rad poster. Startraden som ska returneras bestäms av FÖRSKJUTNINGSVÄRDET och det maximala antalet rader som ska returneras från den punkten genom att hämta.

alla exempel på den här lektionen är baserade på Microsoft SQL Server Management Studio och AdventureWorks2012-databasen. Du kan komma igång med att använda dessa gratis verktyg med min Guide komma igång med SQL Server.

att använda OFFSET och hämta med ORDER BY-klausulen

att returnera rader från ett SQL-uttalande kan vara en allt eller inget-affär. I många fall är antalet rader som returneras mycket stort och det kan orsaka problem om du bara behöver en del av resultatuppsättningen.

när resultaten sorteras med hjälp av ORDER BY-satsen, kommer vissa alternativ att spela för att begränsa antalet returnerade rader:

  1. Du kan använda TOP för att returnera ett visst antal rader.
  2. Du kan använda OFFSET och hämta.

i den här artikeln dyker vi in i Läs mer om OFFSET och hämta. För att lära dig mer om TOP, Läs artikeln komma igång med SQL Server: 2. Sortera Dina Frågeresultat.

OFFSET

argumentet OFFSET används för att identifiera startpunkten för att returnera rader från ett resultat. OFFESET kallas ett argument eftersom det är tekniskt en del av ORDER BY-klausulen. Förskjutningen är antalet rader att hoppa över innan du inkluderar dem i resultatet.

den allmänna formen för OFFSETARGUMENTET är:

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

där raderna till skip är ett värde större än eller lika med noll.

till exempel, för att visa alla utom de första 10 anställda, beställda av HireDate kan du skriva

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

här är några saker att tänka på om OFFSET

  • OFFSET är en del av ORDER BY-klausulen. Det kan inte användas på egen hand.
  • offsetvärden måste vara noll eller högre. Ett negativt tal resulterar i ett fel.
  • när OFFSET är 0 hoppas inga rader över.
  • Om OFFSET är större än antalet rader i de ordnade resultaten returneras inga rader.

hämta

HÄMTNINGSARGUMENTET används för att returnera ett visst antal rader. FETCH kan inte användas av sig själv, det används tillsammans med OFFSET.

fortsätter med vårt exempel kan vi visa de 11: e till 15: e anställda som anställts med hjälp av detta uttalande

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

i följande diagram kan du se OFFSET och hämta på jobbet. OFFSET används för att hoppa över de första 10 raderna och hämta används sedan för att visa nästa 5.

OFFSET och hämta fönsterdata
OFFSET och hämta i aktion

kombinationen av OFFSET och FETCH gör det enkelt att hämta ett” glidande ” fönster med rader. Fönstrets början bestäms av förskjutning och höjden genom hämtning.

användningar för OFFSET och FETCH

personsökning

en av de mest populära användningarna för OFFSET och FETCH är personsökning. Utan tvekan har du besökt webbplats där du ser en lista med objekt och längst ner finns en lista med sidnummer eller en nästa knapp.

vi använder personsökning hela tiden på webben. Det mest populära exemplet jag kan tänka på är Google:

Google Paging
Google Paging Bar

siffrorna nedan Google representerar sidnummer. En del av sökresultaten returneras med varje klick.

på liknande sätt antar vi att vi har en webbsida som visar anställda av HireDate. Om vi ville visa 20 anställda på en sida och vi visade 3: e sidan (anställda 21-30) kunde vi använda följande fråga:

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

den här frågan instruerar SQL att:

  1. Välj medarbetarinformation
  2. Beställ informationen med HireDate
  3. hoppa över 20 rader och börja visa resultat från den 21: a
  4. Visa nästa 10 rader med resultat.

hämta Toppposter

om du vill få de tio bästa raderna i en fråga kan du göra det genom att ställa in OFFSET till 0. Kom ihåg att förskjutningen anger antalet rader som ska hoppa över. Genom att ställa in den till noll, säger Vi SQL att börja på första raden.

När OFFSET är inställt är nästa arbetsplan att ställa in Hämta. Eftersom vi letar efter de tio bästa ställer vi Hämta bredvid 10.

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

Om du är bekant med TOP-klausulen kanske du har märkt att det finns likheter mellan dessa två metoder. Till exempel till ovanstående exempel med TOP skulle se ut som

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

det finns aspekter till toppen som inte gäller för OFFSET och FETCH; som att vara tillåtet i uttalanden utan en ORDER av, men som du kan se för det här exemplet returnerar de motsvarande resultat.

hämta Bottenposter

för att få bottenposterna i en resultatuppsättning finns det två sätt. Den första är att beställa resultatet i fallande ordning i motsats till stigande. Detta placerar de nedre resultaten till toppen. Då kan du använda fetch som vanligt.

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

Om du inte vill ändra ordningen på resultaten kan du använda en underfråga för att få rekordantalet. Detta kan sedan användas för att ställa in förskjutningen i enlighet därmed.

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

det finns en viss fara i denna metod eftersom uttrycket för att beräkna OFFSET kan resultera i ett värde mindre än noll. I vårt exempel kan detta hända om det totala antalet rader var mindre än tio. För att försvara sig mot detta villkor skulle du vilja införliva ett fall uttalande i din logik för att kontrollera mot detta villkor.

Samplingsdata i mitten av en resultatuppsättning

en trevlig funktion för att hämta och kompensera du kan skriva SQL för att prova eller testa data från mitten av resultatet. Detta är en riktigt praktisk om du behöver se ta en titt på mitten av en resultatuppsättning, kanske en som innehåller miljontals rader, utan att visa alla rader upp till den punkt du vill granska.

för att prova i mitten skulle du använda samma logik som för personsökning. Naturligtvis kan antalet rader du hämtar i det här fallet vara mycket större.

effekt av ORDER BY på OFFSET och FETCH

OFFSET och FETCH fungerar bara tillsammans med en ORDER BY-klausul. I själva verket hämtar SQL först de data du angav, till exempel kolumner, och beställer sedan data i stigande eller fallande ordning.

först efter att detta steg har slutförts hoppas raderna över och resultaten produceras.

gränsfall

eftersom vissa värden kan resultera i ett fel, till exempel en negativ förskjutning, låt oss undersöka olika kombinationer av värden som kan överstiga antalet rader i en tabell för att förstå vilka värden som är säkra att använda och vilka värden som skulle kasta ett SQL-fel.

Vi baserar våra exempel på mänskliga resurser.Anställd tabell, som innehåller 290 rader.

OFFSET och hämta juridiska värden
gränsvillkor för OFFSET och FETCH

det enda fallet som resulterar i ett fel är när OFFSET är negativt. Det finns fall som inte returnerar rader, eller kan returnera mindre rader än du tror, men dessa fall kastar inte fel. För det mesta uppstår dessa situationer när antingen OFFSETVÄRDET är större än antalet rader i resultatet (alla rader hoppas över), eller om du försöker hämta ”förbi” sedan slutet av resultatet.

Lämna ett svar

Din e-postadress kommer inte publiceras.