Sie können so etwas versuchen
DECLARE @Table TABLE(
Val VARCHAR(50)
)
DECLARE @PageSize INT,
@Page INT
SELECT @PageSize = 10,
@Page = 2
;WITH PageNumbers AS(
SELECT Val,
ROW_NUMBER() OVER(ORDER BY Val) ID
FROM @Table
)
SELECT *
FROM PageNumbers
WHERE ID BETWEEN ((@Page - 1) * @PageSize + 1)
AND (@Page * @PageSize)
Sie können ROW_NUMBER () verwenden:
Gibt die laufende Nummer einer Zeile innerhalb einer Partition einer Ergebnismenge zurück, beginnend bei 1 für die erste Zeile in jeder Partition.
Beispiel:
WITH CTEResults AS
(
SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
FROM MyTable
)
SELECT *
FROM CTEResults
WHERE RowNum BETWEEN 10 AND 20;
SQL Server 2012 bietet die Paginierungsfunktion (siehe http://www.codeproject.com/Articles/442503/NeueFeatures-für -Datenbank-entwickler-in-SQL-Server ).
In SQL2008 können Sie dies folgendermaßen tun:
declare @rowsPerPage as bigint;
declare @pageNum as bigint;
set @rowsPerPage=25;
set @pageNum=10;
With SQLPaging As (
Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc)
as resultNum, *
FROM Employee )
select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)
Bewiesen! Es funktioniert und skaliert beständig.
1) DUMMY-DATEN ERSTELLEN
CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20))
DECLARE @id INT = 1
WHILE @id < 200
BEGIN
INSERT INTO #employee ( NAME ) VALUES ('employee_' + CAST(@id AS VARCHAR) )
SET @id = @id + 1
END
2) JETZT DIE LÖSUNG ANWENDEN.
In diesem Fall wird davon ausgegangen, dass die EMPID eindeutig und die Spalte sortiert ist.
Natürlich werden Sie es in einer anderen Spalte anwenden ...
DECLARE @pageSize INT = 20
SELECT * FROM (
SELECT *, PageNumber = CEILING(CAST(EMPID AS FLOAT)/@pageSize)
FROM #employee
) MyQuery
WHERE MyQuery.PageNumber = 1
Dies ist meine Lösung für das Paging des Abfrageergebnisses auf der SQL Server-Seite. Ich habe das Konzept der Filterung und Sortierung mit einer Spalte hinzugefügt. Es ist sehr effizient, wenn Sie in Ihrem Gridview blättern, filtern und ordnen.
Vor dem Test müssen Sie eine Beispieltabelle erstellen und eine Zeile in diese Tabelle einfügen: (In der Praxis müssen Sie die Where-Klausel hinsichtlich Ihres Tabellenfelds ändern und haben möglicherweise Join und Unterabfragen im Hauptteil von select.
Create Table VLT
(
ID int IDentity(1,1),
Name nvarchar(50),
Tel Varchar(20)
)
GO
Insert INTO VLT
VALUES
('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity))
GO 500000
In SQL Server 2008 können Sie das CTE-Konzept verwenden. Aus diesem Grund habe ich zwei Arten von Abfragen für SQL Server 2008+ geschrieben
- SQL Server 2008+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
GO
Und zweite Lösung mit CTE in SQL Server 2008+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
;WITH
Data_CTE
AS
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
)
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
Eine andere Lösung, die zumindest von SQL 2005 aus funktioniert, ist die Verwendung von TOP mit SELECT-Unterabfragen und ORDER BY-Klauseln.
Kurz gesagt, das Abrufen von 2 Zeilen mit 10 Zeilen pro Seite entspricht dem Abrufen der letzten 10 Zeilen der ersten 20 Zeilen. Was bedeutet, die ersten 20 Zeilen mit ASC-Reihenfolge und dann die ersten 10 Zeilen mit DESC-Reihenfolge abzurufen, bevor sie erneut mit ASC bestellt werden.
Beispiel: Seite 2 Zeilen mit 3 Zeilen pro Seite abrufen
create table test(id integer);
insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
select *
from (
select top 2 *
from (
select top (4) *
from test
order by id asc) tmp1
order by id desc) tmp1
order by id asc