w tym artykule zbadamy klauzule OFFSET i FETCH. OFFSET i FETCH są używane w połączeniu z klauzulą select ORDER BY, aby zapewnić sposób pobierania zakresu rekordów. Początkowy wiersz do zwrócenia jest określony przez wartość OFFSET i maksymalną liczbę wierszy do zwrócenia od tego punktu przez FETCH.
wszystkie przykłady tej lekcji są oparte na Microsoft SQL Server Management Studio i Bazie Danych AdventureWorks2012. Możesz zacząć korzystać z tych bezpłatnych narzędzi, korzystając z mojego przewodnika Pierwsze kroki przy użyciu SQL Server.
użycie offsetu i FETCH z klauzulą ORDER BY
zwracanie wierszy z instrukcji SQL może być sprawą all or nothing. W wielu przypadkach liczba zwracanych wierszy jest bardzo duża i może to powodować problemy, jeśli potrzebujesz tylko części zestawu wyników.
gdy wyniki są sortowane za pomocą klauzuli ORDER BY, wtedy niektóre opcje wchodzą w grę, aby ograniczyć liczbę zwracanych wierszy:
- możesz użyć TOP, aby zwrócić określoną liczbę wierszy.
- możesz użyć offsetu i pobrać.
w tym artykule przyjrzymy się bliżej OFFSETOWI i FETCHOWI. Aby dowiedzieć się więcej o TOP, przeczytaj artykuł Wprowadzenie do SQL Server: 2. Sortuj Wyniki Zapytania.
OFFSET
argument OFFSET służy do identyfikacji punktu początkowego zwracania wierszy z wyniku. OFFESET jest nazywany argumentem, ponieważ technicznie jest częścią klauzuli ORDER BY. Przesunięcie to liczba wierszy, które należy pominąć przed włączeniem ich do wyniku.
ogólną formą argumentu OFFSET jest:
SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS
gdzie wiersze do pominięcia mają wartość większą lub równą zero.
na przykład, aby pokazać wszystkich oprócz pierwszych 10 pracowników, zamówionych przez HireDate, możesz napisać
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS
oto kilka rzeczy do rozważenia o offsecie
- OFFSET jest częścią klauzuli ORDER BY. Nie może być stosowany samodzielnie.
- wartości offsetu muszą być równe zero lub większe. Liczba ujemna powoduje błąd.
- gdy OFFSET wynosi 0, wtedy żadne wiersze nie są pomijane.
- jeżeli OFFSET jest większy niż ilość wierszy w uporządkowanych wynikach, wtedy nie są zwracane żadne wiersze.
FETCH
argument FETCH służy do zwrócenia ustawionej liczby wierszy. FETCH nie może być użyty sam, jest używany w połączeniu z offsetem.
kontynuując nasz przykład, możemy pokazać 11.do 15. pracowników zatrudnionych za pomocą tego Oświadczenia
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
na poniższym diagramie możesz zobaczyć OFFSET i pobrać w pracy. OFFSET jest używany do pominięcia pierwszych 10 wierszy, a FETCH jest używany do wyświetlenia kolejnych 5.
kombinacja przesunięcia i pobierania ułatwia pobieranie „przesuwanego” okna wierszy. Początek okna jest określony przez OFFSET, a wysokość przez FETCH.
używa offsetu i FETCH
Paging
jednym z najpopularniejszych zastosowań offsetu i FETCH jest paging. Bez wątpienia odwiedziłeś stronę internetową, na której widzisz listę elementów, a na dole znajduje się lista numerów stron lub przycisk Dalej.
używamy paging cały czas w Internecie. Najpopularniejszym przykładem jest Google:
liczby poniżej Google reprezentują numery stron. Część wyników wyszukiwania jest zwracana za każdym kliknięciem.
w podobny sposób Załóżmy, że mamy stronę internetową wyświetlającą pracowników przez HireDate. Jeśli chcemy wyświetlić 20 pracowników na stronie, a wyświetlamy stronę trzecią (pracownicy 21-30), możemy użyć następującego zapytania:
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
to zapytanie instruuje SQL do:
- wybierz Informacje o pracowniku
- Zamów informacje przez HireDate
- Pomiń 20 wierszy i rozpocznij wyświetlanie wyników od 21.
- wyświetl następne 10 wierszy wyników.
uzyskiwanie najlepszych rekordów
Jeśli chcesz uzyskać dziesięć pierwszych wierszy w zapytaniu, możesz to zrobić, ustawiając OFFSET na 0. Pamiętaj, że OFFSET określa liczbę wierszy do pominięcia. Ustawiając go na zero, mówimy SQL ’ owi, aby zaczynał od pierwszego wiersza.
Po ustawieniu offsetu następną kolejnością jest ustawienie FETCH. Ponieważ szukamy pierwszej dziesiątki, ustawiamy FETCH obok 10.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Jeśli znasz klauzulę TOP, być może zauważyłeś podobieństwa między tymi dwiema metodami. Na przykład w powyższym przykładzie użycie TOP wyglądałoby tak
SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate
istnieją aspekty do góry, które nie mają zastosowania do offsetu i pobierania; takie jak dozwolone w poleceniach bez kolejności przez, ale jak widać w tym przykładzie, zwracają one równoważne wyniki.
pobieranie dolnych rekordów
aby uzyskać dolne rekordy w zestawie wyników, istnieją dwa sposoby. Pierwszym z nich jest uporządkowanie wyniku w porządku malejącym, a nie rosnącym. To umieszcza wyniki od dołu do góry. Następnie możesz użyć fetch jak zwykle.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Jeśli nie chcesz zmieniać kolejności wyników, możesz użyć zapytania podrzędnego, aby uzyskać liczbę rekordów. Można to następnie wykorzystać do odpowiedniego ustawienia offsetu.
SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
w tej metodzie istnieje pewne niebezpieczeństwo, ponieważ wyrażenie do obliczenia offsetu może skutkować wartością mniejszą niż zero. W naszym przykładzie może się to zdarzyć, jeśli całkowita liczba wierszy była mniejsza niż dziesięć. Aby bronić się przed tym warunkiem, chciałbyś włączyć do swojej logiki oświadczenie przypadku, aby sprawdzić ten warunek.
próbkowanie danych w środku zestawu wyników
jedną z ładnych funkcji FETCH i OFFSET możesz zapisać SQL do próbkowania lub testowania danych ze środka wyniku. Jest to bardzo przydatne, jeśli chcesz zobaczyć zerknąć na środek zestawu wyników, być może takiego, który zawiera miliony wierszy, bez wyświetlania wszystkich wierszy do punktu, który chcesz przejrzeć.
aby spróbować w środku, użyłbyś tej samej logiki, co w przypadku stronicowania. Oczywiście liczba wierszy, które pobierasz w tym przypadku, może być znacznie większa.
efekt ORDER BY na offsecie i FETCH
OFFSET i FETCH działają tylko w połączeniu z klauzulą ORDER BY. W rezultacie SQL najpierw pobiera określone dane, takie jak kolumny, a następnie porządkuje dane w porządku rosnącym lub malejącym.
dopiero po zakończeniu tego kroku wiersze są pomijane i generowane wyniki.
przypadki graniczne
ponieważ niektóre wartości mogą powodować błąd, taki jak ujemne przesunięcie, zbadajmy różne kombinacje wartości, które mogą przekraczać liczbę wierszy w tabeli, aby zrozumieć, które wartości są bezpieczne w użyciu, a które wartości spowodowałyby błąd SQL.
oprzemy nasze przykłady z zasobów ludzkich.Tabela pracowników, która zawiera 290 wierszy.
jedynym przypadkiem, który powoduje błąd, jest sytuacja, gdy przesunięcie jest ujemne. Istnieją przypadki, które nie zwracają wierszy lub mogą zwracać mniej wierszy niż myślisz, ale te przypadki nie powodują błędów. W większości przypadków takie sytuacje występują, gdy wartość offsetu jest większa niż liczba wierszy w wyniku (wszystkie wiersze są pomijane) lub próbujesz pobrać „past”, a następnie zakończyć wynik.