在SQL Server 2000、2005、2008、2012中分页结果的最佳方法(性能方面)是什么?如果你还想获得结果的总数(在分页之前)?
当前回答
试试这个方法:
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 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
最后，微软SQL Server 2012发布了，我真的很喜欢它的简单的分页，你不需要使用复杂的查询，就像这里回答的。
要获得接下来的10行，只需运行这个查询:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned
使用时需要考虑的要点:
ORDER BY是强制使用OFFSET…获取子句。 OFFSET子句在FETCH中是强制的。你不能使用ORDER BY… 取回。 在同一个查询中，TOP不能与OFFSET和FETCH组合使用 表达式。
对于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。
好吧，我已经在我的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文本。
这些是我在SQL server端对查询结果进行分页的解决方案。 这些方法在SQL Server 2008和2012之间是不同的。 此外，我还添加了过滤和排序的概念。当你在Gridview中分页、过滤和排序时，它是非常高效的。
在测试之前，你必须创建一个示例表，并在这个表中插入一些行:(在现实世界中，你必须改变Where子句，考虑到你的表字段，也许你有一些连接和子查询在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
在所有这些示例中，我想查询每页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;
推荐文章
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
- 如何改变字符集(和排序)在整个数据库?
- 就性能而言，使用std::memcpy()还是std::copy()更好?
- mySQL:: insert到表，数据从另一个表?
- nvarchar(max)非文本
- 如何在Postgres中获得两个字段的MIN() ?
- 什么时候我应该(不)想要在我的代码中使用熊猫apply() ?
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
- 修改列，添加默认约束
