sivutusta käytetään usein sovelluksissa, joissa käyttäjä voi napsauttaa Edellinen / Seuraava navigoidakseen sivut, jotka muodostavat tulokset, tai klikata sivunumeroa siirtyäkseen suoraan tietylle sivulle.
kun kyselyjä ajetaan SQL Server-palvelussa, tulokset voidaan sivuttaa käyttämällä OFFSET ja FETCH argumentteja ORDER BY lauseketta. Nämä argumentit otettiin käyttöön SQL Server 2012: ssa, joten voit käyttää tätä tekniikkaa, jos sinulla on SQL Server 2012 tai uudempi.
tässä yhteydessä sivutus on se, jossa kyselyn tulokset jaetaan pienempiin paloihin, joista kukin jatkaa edellisen loppuun. Jos kysely esimerkiksi palauttaa 1000 riviä, voit pakinoida ne niin, että ne palautetaan 100: n ryhmissä. Sovellus voi siirtää sivunumeron ja sivukoon SQL Serverille, ja SQL Server voi sitten käyttää sitä vain pyydetyn sivun tietojen palauttamiseen.
Esimerkki 1-ei Sivuamista
ensin ajetaan kysely, joka palauttaa kaikki taulukon rivit:
SELECT *FROM GenresORDER BY GenreId;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
tässä esimerkissä ei käytetä sivutusta – kaikki tulokset näytetään.
tämä tulosjoukko on niin pieni, ettei se normaalisti vaatisi sivutusta, mutta tätä artikkelia varten sivuutetaan se.
Esimerkki 2 – Näytä 3 ensimmäistä tulosta
Tämä esimerkki näyttää kolme ensimmäistä tulosta:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
tässä tapauksessa tarkennan, että tulosten tulee alkaa ensimmäisestä tuloksesta ja näyttää kolme seuraavaa riviä. Tämä tehdään käyttämällä seuraavia:
-
OFFSET 0 ROWSmäärittää, että offsetia ei saa olla (offset on nolla). -
FETCH NEXT 3 ROWS ONLYsaa seuraavat kolme riviä offsetista. Koska olen määrittänyt offset nolla, kolme ensimmäistä riviä haetaan.
Jos olisimme halunneet vain top 3-tuloksen, olisimme voineet saavuttaa saman tuloksen käyttämällä TOP – lauseketta sen sijaan, että olisimme määrittäneet offset-ja fetch-arvot. Tämä ei kuitenkaan olisi mahdollistanut seuraavaa osaa.
esimerkki 3 – Näytä seuraavat 3 tulosta
nyt näytetään kolme seuraavaa tulosta:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
niin ainoa asia, mitä muutin, oli offset.
offset-ja fetching-arvot voivat olla myös lauseke, joka annetaan muuttuvana, parametrina tai jatkuvana skalaarisena alikvenssinä. Kun alikuvausta käytetään, se ei voi viitata mihinkään ulommassa kyselyalueella määriteltyyn sarakkeeseen (sitä ei voi korreloida ulomman kyselyn kanssa).
seuraavissa esimerkeissä käytetään ilmaisuja, jotka osoittavat kaksi lähestymistapaa tulosten sivuuttamiseen.
esimerkki 4 – sivutus rivinumeron mukaan
tässä esimerkissä käytetään lausekkeita alkavan rivinumeron määrittelemiseksi.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
tässä käytän @StartRow int = 1 täsmentääkseni, että tulosten pitäisi alkaa ensimmäiseltä riviltä.
näin käy, jos lisään tuon arvon 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
se alkaa toiselta riviltä. Tällä menetelmällä, voin määrittää tarkka rivi alkaa.
esimerkki 5 – sivutus sivunumeron mukaan
Tämä esimerkki on lähes identtinen edellisen esimerkin kanssa, paitsi että siinä voidaan määrittää sivunumero rivinumeron sijaan.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
joten ensimmäinen tulos on sama. Katsotaan kuitenkin, mitä tapahtuu, kun kasvamme @PageNumber2 (Nimesin tämän muuttujan vastaamaan sen uutta tarkoitusta).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
tällä kertaa tulokset alkavat neljänneltä riviltä. Joten käyttämällä tätä menetelmää voit yksinkertaisesti siirtää sivunumeron sijaan rivinumero.
esimerkki 6 – Sivusilmukka
loppuun, tässä nopea esimerkki, joka silmukoi kaikki sivut läpi ja määrittää jokaisen iteraation lähtörivin numeron:
DECLARE @StartRow int = 1, @RowsPerPage int = 3;WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow BEGIN SELECT * FROM Genres ORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;SET @StartRow = @StartRow + @RowsPerPage; CONTINUEEND;
tulos:
esimerkki 7 – rivinen vs rivit
Jos kohtaat koodi, jossa käytetään ROW sijasta ROWS, molemmat argumentit tekevät saman asian. Ne ovat synonyymejä, ja ne on tarkoitettu ANSI-yhteensopivuuteen.
tässä ensimmäinen esimerkki tällä sivulla, mutta ROW sijaan ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
esimerkki 8 – FIRST vs NEXT
sama koskee FIRST ja NEXT. Nämä ovat ANSI-yhteensopivuuden synonyymejä.
tässä on edellinen esimerkki, mutta FIRST sijaan NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
tulos:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+