在 SQL Server 中对结果进行分页的最佳方法是什么
如果您还想获得结果的总数（在分页之前），那么在 SQL Server 2000、2005、2008、2012 中对结果进行分页的最佳方法（性能方面）是什么？
最后，Microsoft SQL Server 2012 发布了，我真的很喜欢它对分页的简单性，您不必使用这里回答的复杂查询。
要获取接下来的 10 行，只需运行以下查询：
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
使用时要考虑的要点：
ORDER BY 是强制使用 OFFSET ... FETCH 子句的。
OFFSET 子句对于 FETCH 是强制性的。您不能使用 ORDER BY ... FETCH。
TOP 不能在同一个查询表达式中与 OFFSET 和 FETCH 结合使用。
获取结果总数和分页是两个不同的操作。为了这个例子，让我们假设您正在处理的查询是
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
在这种情况下，您将使用以下方法确定结果总数：
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...这可能看起来效率低下，但实际上性能相当不错，假设所有索引等都已正确设置。
接下来，要以分页方式返回实际结果，以下查询将是最有效的：
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
这将返回原始查询的第 1-19 行。这里很酷的事情，尤其是对于网络应用程序，是您不必保留任何状态，除了要返回的行号。
令人难以置信的是，没有其他答案提到在所有 SQL Server 版本中进行分页的最快方式。对于大页码，偏移量可能会非常慢，就像 benchmarked here。在 SQL 中执行分页有一种完全不同的、更快的方法。这通常称为 this blog post here 中描述的“搜索方法”或“键集分页”。
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
“寻找谓词”
@previousScore 和 @previousPlayerId 值分别是上一页最后一条记录的值。这允许您获取“下一页”。如果 ORDER BY 方向是 ASC，只需使用 > 代替。
使用上述方法，您不能在没有先获取前 40 条记录的情况下立即跳转到第 4 页。但通常，无论如何你都不想跳那么远。相反，您将获得更快的查询，该查询可能能够在恒定时间内获取数据，具体取决于您的索引。另外，您的页面保持“稳定”，无论基础数据是否发生变化（例如，在第 1 页，而您在第 4 页）。
例如，当在 Web 应用程序中延迟加载更多数据时，这是实现分页的最佳方式。
请注意，“seek 方法”也称为 keyset pagination。
分页前的总记录
COUNT(*) OVER() 窗口函数将帮助您计算“分页前”的总记录数。如果您使用的是 SQL Server 2000，您将不得不对 COUNT(*) 使用两个查询。
OFFSET .. FETCH 还是使用以前的 ROW_NUMBER() 技巧实现偏移分页，键集分页总是优于偏移分页。
RowNumber 给我每页一致的 10 个项目。 [3] 它不适用于假设 pagenumber 和 pagesize 的现有网格。
从 SQL Server 2012 开始，我们可以使用 OFFSET 和 FETCH NEXT 子句来实现分页。
试试这个，对于 SQL Server：
在 SQL Server 2012 中，在 ORDER BY 子句中添加了一项新功能，用于查询优化一组数据，使任何使用 T-SQL 编写的人以及 SQL Server 中的整个执行计划都可以更轻松地进行数据分页。在 T-SQL 脚本下方，其逻辑与前一个示例中使用的逻辑相同。 --在“SQL SERVER 2012”中使用 OFFSET 和 FETCH 子句创建分页 DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 10 SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ORDER BY ID_EXAMPLE OFFSET (( @PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;
TechNet: Paging a Query with SQL Server
MSDN: ROW_NUMBER (Transact-SQL)
返回结果集分区内行的序号，每个分区的第一行从 1 开始。以下示例按照 OrderDate 的顺序返回编号为 50 到 60 的行。
WITH OrderedOrders AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM [dbo].[vSalesPerson]
)
SELECT RowNumber,
FirstName, LastName, Sales YTD
FROM OrderedOrders
WHERE RowNumber > 50 AND RowNumber < 60;
RowNumber FirstName LastName SalesYTD
--- ----------- ---------------------- -----------------
1 Linda Mitchell 4251368.54
2 Jae Pak 4116871.22
3 Michael Blythe 3763178.17
4 Jillian Carson 3189418.36
5 Ranjit Varkey Chudukatil 3121616.32
6 José Saraiva 2604540.71
7 Shu Ito 2458535.61
8 Tsvi Reiter 2315185.61
9 Rachel Valdez 1827066.71
10 Tete Mensa-Annan 1576562.19
11 David Campbell 1573012.93
12 Garrett Vargas 1453719.46
13 Lynn Tsoflias 1421810.92
14 Pamela Ansman-Wolfe 1352577.13
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx 对不同的分页技术进行了很好的概述
我经常在 SQL Server 2000 中使用 ROWCOUNT 方法（也适用于 2005 和 2008，只是测量与 ROW_NUMBER 相比的性能），它快如闪电，但您需要确保已排序的列（主要是) 独特的价值。
对于 SQL Server 2000，您可以使用带有 IDENTITY 列的表变量来模拟 ROW_NUMBER()：
DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20
DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1 -- 1020
DECLARE @orderedKeys TABLE (
rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
TableKey int NOT NULL
)
SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
SET ROWCOUNT 0
SELECT t.*
FROM Orders t
INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum
这种方法可以扩展到具有多列键的表，并且不会产生使用 OR 的性能开销（跳过索引使用）。不利的一面是，如果数据集非常大并且靠近最后一页，则会占用大量临时空间。在那种情况下，我没有测试游标性能，但它可能会更好。
请注意，这种方法可以针对第一页数据进行优化。此外，由于 TOP 在 SQL Server 2000 中不接受变量，因此使用了 ROWCOUNT。
在 sql server 2012 中进行分页的最佳方式是在存储过程中使用偏移量和获取下一个。 OFFSET 关键字 - 如果我们在 order by 子句中使用偏移量，那么查询将跳过我们在 OFFSET n Rows 中指定的记录数。
FETCH NEXT 关键字 - 当我们将 Fetch Next 与 order by 子句一起使用时，它只会返回要在分页中显示的行数，如果没有 Offset，则 SQL 将生成错误。这是下面给出的示例。
create procedure sp_paging
(
@pageno as int,
@records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end
您可以按如下方式执行它。
exec sp_paging 2,3
试试这个方法：
SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows
from TABLENAME b order by id asc) a
ORDER BY id desc;
这些是我在 SQL 服务器端对查询结果进行分页的解决方案。这些方法在 SQL Server 2008 和 2012 之间是不同的。此外，我还添加了筛选和排序的概念。当您在 Gridview 中进行分页、过滤和排序时，它非常有效。
在测试之前，您必须创建一个示例表并在该表中插入一些行：（在现实世界中，您必须考虑您的表字段来更改 Where 子句，也许您在选择的主要部分有一些连接和子查询）
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
在所有这些示例中，我想每页查询 200 行，并且我正在获取第 1200 页的行。
在 SQL server 2008 中，您可以使用 CTE 概念。因此，我为 SQL Server 2008+ 编写了两种类型的查询
-- 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
在 SQL Server 2008+ 中使用 CTE 的第二个解决方案
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
-- SQL Server 2012+
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
*
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
ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN Data.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN Data.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN Data.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN Data.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN Data.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN Data.Tel END ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
从 2012 年起，我们可以使用 OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
用例明智，以下似乎易于使用且快速。只需设置页码。
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
也没有 CTE
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
AND 的子句，并在需要时添加注释或删除它们。
这是 2012 年旧 SO 问题的副本：efficient way to implement paging
FROM [TableX] ORDER BY [FieldX] OFFSET 500 ROWS FETCH NEXT 100 ROWS ONLY
Here 更详细地讨论了该主题，并采用了替代方法。
好吧，我在我的 SQL 2000 数据库中使用了以下示例查询，它也适用于 SQL 2005。它为您提供的功能是通过使用多个列来动态排序。我告诉你......这很强大:)
ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary]
@CompanyID int,
@pageNumber int,
@pageSize int,
@sort varchar(200)
AS
DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)
If(@pageNumber < 0)
SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20))
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For example if pageNumber is 5 pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID,
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '
SET @strFilter = ' WHERE
CompanyID = ' + CAST(@CompanyID As varchar(20))
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort
-- Total Rows Count
SET @sql = 'SELECT Count(' + @strID + ') FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql
--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
' WHERE ' + @strID + ' IN ' +
' (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter +
' AND ' + @strID + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') '
+ @SortBy + ') ' + @SortBy
Print @sql
EXEC sp_executesql @sql
最好的部分是 sp_executesql 缓存以后的调用，前提是您传递相同的参数，即生成相同的 sql 文本。
CREATE view vw_sppb_part_listsource as
select row_number() over (partition by sppb_part.init_id order by sppb_part.sppb_part_id asc ) as idx, * from (
select
part.SPPB_PART_ID
, 0 as is_rev
, part.part_number
, part.init_id
from t_sppb_init_part part
left join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
where prev.SPPB_PART_ID is null
union
select
part.SPPB_PART_ID
, 1 as is_rev
, prev.part_number
, part.init_id
from t_sppb_init_part part
inner join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
) sppb_part
当涉及到不同的 init_id 时将重新启动 idx
对于 ROW_NUMBER 技术，如果您没有要使用的排序列，则可以使用 CURRENT_TIMESTAMP，如下所示：
SELECT TOP 20
col1,
col2,
col3,
col4
FROM (
SELECT
tbl.col1 AS col1
,tbl.col2 AS col2
,tbl.col3 AS col3
,tbl.col4 AS col4
,ROW_NUMBER() OVER (
ORDER BY CURRENT_TIMESTAMP
) AS sort_row
FROM dbo.MyTable tbl
) AS query
WHERE query.sort_row > 10
ORDER BY query.sort_row
这对我来说非常适合搜索甚至高达 700,000 的表大小。
这将获取记录 11 到 30。
创建 PROCEDURE SP_Company_List (@pagesize int = -1 ,@pageindex int= 0 ) > AS BEGIN SET NOCOUNT ON; select Id , NameEn from Company ORDER by Id ASC OFFSET (@pageindex-1)* @pagesize ROWS FETCH NEXt @pagesize ROWS ONLY END GO DECLARE @return_value int EXEC @return_value = [dbo].[SP_Company_List] @pagesize = 1 , > @pageindex = 2 SELECT '返回值' = @return_value GO
此位使您能够使用 SQL Server 和更新版本的 MySQL 进行分页，并携带每行中的总行数。使用您的主键来计算唯一行的数量。
WITH T AS
(
SELECT TABLE_ID, ROW_NUMBER() OVER (ORDER BY TABLE_ID) AS RN
, (SELECT COUNT(TABLE_ID) FROM TABLE) AS TOTAL
FROM TABLE (NOLOCK)
)
SELECT T2.FIELD1, T2.FIELD2, T2.FIELD3, T.TOTAL
FROM TABLE T2 (NOLOCK)
INNER JOIN T ON T2.TABLE_ID=T.TABLE_ID
WHERE T.RN >= 100
AND T.RN < 200
您没有指定语言或您使用的驱动程序。所以我抽象地描述它。
创建一个可滚动的结果集/数据集。这需要表上的主节点
跳到最后
请求行数
跳转到页面的开头
滚动行直到页面末尾
关注公众号
不定期副业成功案例分享
LISTAGG()/GROUP_CONCAT()。FOR XML的偷偷摸摸的方法：stackoverflow.com/a/273330/429949FOR XML PATH ('')有很多问题。首先，它将 XML 控制字符替换为 XML 实体代码。希望您的数据中没有<、>或&！其次，以这种方式使用的FOR XML PATH ('')实际上是未记录的语法。您应该指定命名列或备用元素名称。文档中没有这样做，这意味着该行为是不可靠的。第三，我们越是接受损坏的FOR XML PATH ('')语法，MS 就越不可能像他们需要的那样实际提供 realLISTAGG() [ OVER() ]函数。