Odpowiednik LIMIT i OFFSET dla SQL Server?

W PostgreSQL są słowa kluczowe Limiti, Offsetktóre pozwolą na bardzo łatwe paginowanie zbiorów wynikowych.

Jaka jest równoważna składnia dla programu SQL Server?

Odpowiednik LIMITis SET ROWCOUNT, ale jeśli chcesz mieć ogólną paginację, lepiej napisać takie zapytanie:

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

Zaletą jest tutaj parametryzacja przesunięcia i limitu w przypadku, gdy zdecydujesz się zmienić opcje stronicowania (lub zezwolisz na to użytkownikowi).

Uwaga:@Offset parametr powinien używać jednej opartej indeksowanie za to zamiast normalnego indeksowania opartego na zero.

Ta funkcja jest teraz uproszczona w programie SQL Server 2012. Działa od wersji SQL Server 2012.

Ogranicz z przesunięciem, aby wybrać od 11 do 20 wierszy w programie SQL Server:

SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

• OFFSET: liczba pominiętych wierszy
• NEXT: wymagana liczba następnych rzędów

Źródła: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017

select top {LIMIT HERE} * from (
      select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n 
      from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}
) xx where r_n_n >={OFFSET HERE}

Uwaga: to rozwiązanie będzie działać tylko w SQL Server 2005 lub nowszym, ponieważ wtedy ROW_NUMBER()zostało wdrożone.

Aby to osiągnąć, możesz użyć ROW_NUMBER we wspólnym wyrażeniu tabelowym.

;WITH My_CTE AS
(
     SELECT
          col1,
          col2,
          ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
)
SELECT
     col1,
     col2
FROM
     My_CTE
WHERE
     row_number BETWEEN @start_row AND @end_row

Dla mnie użycie OFFSET i FETCH razem było powolne, więc użyłem kombinacji TOP i OFFSET w ten sposób (który był szybszy):

SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

Uwaga: jeśli używasz jednocześnie TOP i OFFSET w tym samym zapytaniu, na przykład:

SELECT TOP 20 columname1, columname2 FROM tablename
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS

Następnie pojawia się błąd, więc aby użyć GÓRA i PRZESUNIĘCIE razem, musisz je oddzielić zapytaniem podrzędnym.

A jeśli potrzebujesz użyć polecenia SELECT DISTINCT, zapytanie wygląda następująco:

SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

Uwaga: użycie SELECT ROW_NUMBER z DISTINCT nie działa dla mnie.

Kolejna próbka:

declare @limit int 
declare @offset int 
set @offset = 2;
set @limit = 20;
declare @count int
declare @idxini int 
declare @idxfim int 
select @idxfim = @offset * @limit
select @idxini = @idxfim - (@limit-1);
WITH paging AS
    (
        SELECT 
             ROW_NUMBER() OVER (order by object_id) AS rowid, *
        FROM 
            sys.objects 
    )
select *
    from 
        (select COUNT(1) as rowqtd from paging) qtd, 
            paging 
    where 
        rowid between @idxini and @idxfim
    order by 
        rowid;

Jest tutaj ktoś, kto mówi o tej funkcji w sql 2011, to smutne, że wybrali trochę inne słowo kluczowe "OFFSET / FETCH", ale nie jest to standard, wtedy ok.

Dodając niewielką różnicę w rozwiązaniu Aaronaught, zwykle parametryzuję numer strony (@PageNum) i rozmiar strony (@PageSize). W ten sposób każde zdarzenie kliknięcia strony wysyła po prostu żądany numer strony wraz z konfigurowalnym rozmiarem strony:

begin
    with My_CTE  as
    (
         SELECT col1,
              ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
    )
    select * from My_CTE
            WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1) 
                              AND @PageNum * @PageSize

end

Najbliżej mogłem zrobić

select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber  and ct <= toNumber

Który chyba podobny select * from [db].[dbo].[table] LIMIT 0, 10

select top (@TakeCount) * --FETCH NEXT
from(
    Select  ROW_NUMBER() OVER (order by StartDate) AS rowid,*
    From YourTable
)A
where Rowid>@SkipCount --OFFSET

@nombre_row :nombre ligne par page  
@page:numero de la page

//--------------code sql---------------

declare  @page int,@nombre_row int;
    set @page='2';
    set @nombre_row=5;
    SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY etudiant_ID ) AS RowNum, *
      FROM      etudiant

    ) AS RowConstrainedResult
WHERE   RowNum >= ((@page-1)*@nombre_row)+1
    AND RowNum < ((@page)*@nombre_row)+1
ORDER BY RowNum

Ponieważ nikt jeszcze nie podał tego kodu:

SELECT TOP @limit f1, f2, f3...
FROM t1
WHERE c1 = v1, c2 > v2...
AND
    t1.id NOT IN
        (SELECT TOP @offset id
         FROM t1
         WHERE c1 = v1, c2 > v2...
         ORDER BY o1, o2...)
ORDER BY o1, o2...

Ważne punkty:

• ORDER BY musi być identyczne
• @limit można zastąpić liczbą wyników do pobrania,
• @offset to liczba wyników do pominięcia
• Porównaj wydajność z poprzednimi rozwiązaniami, ponieważ mogą być bardziej wydajne
• to rozwiązanie jest duplikatem wherei order byklauzulami i zapewni nieprawidłowe wyniki, jeśli nie są zsynchronizowane
• z drugiej strony order byjest wyraźnie, jeśli jest to potrzebne

-- @RowsPerPage  can be a fixed number and @PageNumber number can be passed 
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 2

SELECT *

FROM MemberEmployeeData

ORDER BY EmployeeNumber

OFFSET @PageNumber*@RowsPerPage ROWS

FETCH NEXT 10 ROWS ONLY

Specyficznie dla SQL-SERVER można to osiągnąć na wiele różnych sposobów, dla przykładu wzięliśmy tutaj tabelę Customer.

Przykład 1: z „USTAW ROWCOUNT”

SET ROWCOUNT 10
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName

Aby zwrócić wszystkie wiersze, ustaw ROWCOUNT na 0

SET ROWCOUNT 0  
SELECT CustomerID, CompanyName from Customers
    ORDER BY CompanyName

Przykład 2: z „ROW_NUMBER i OVER”

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
FROM Customers )
select *
from Cust
Where RowNumber Between 0 and 10

Przykład 3: Z „OFFSET and FETCH”, ale z tym „ORDER BY” jest obowiązkowe

SELECT CustomerID, CompanyName FROM Customers
ORDER BY CompanyName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

Mam nadzieję, że to ci pomoże.

Na serwerze SQL użyłbyś TOP razem z ROW_NUMBER ()

Ponieważ testuję więcej razy, ten skrypt jest bardziej przydatny o 1 milion rekordów na każdej stronie. 100 rekordów z paginacją działa szybciej mój komputer wykonuje ten skrypt 0 sekund, podczas gdy w porównaniu z mysql ma własny limit i przesunięcie około 4,5 sekundy, aby uzyskać wynik.

Ktoś może nie zrozumieć, że Row_Number () zawsze sortuje według określonego pola. W przypadku, gdy musimy zdefiniować tylko wiersz w sekwencji, należy użyć:

ROW_NUMBER () OVER (ORDER BY (SELECT NULL))

SELECT TOP {LIMIT} * FROM (
      SELECT TOP {LIMIT} + {OFFSET} ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ROW_NO,*
      FROM  {TABLE_NAME}
) XX WHERE ROW_NO > {OFFSET}

Wyjaśnić:

• {LIMIT}: liczba rekordów dla każdej strony
• {OFFSET}: Liczba pominiętych rekordów