在asp.net gridview中使用ROW_NUMBER()自定义分页

本文关键字：NUMBER 分页 自定义 ROW net asp gridview | 更新日期: 2023-09-27 17:50:26

我试图在sql中使用ROW_NUMBER()在asp.net中实现gridview的自定义分页。下面的(经典)查询工作得很好，但是它的性能不是那么好，因为我必须将整个表复制到一个新表，然后从新表中选择20条记录。有时不进行分页甚至可以更好地工作，只需一次检索所有记录。我试图在where子句中使用ROW_NUMBER()，但sql不允许我这样做。我如何改进下面的查询?你有什么建议吗?我想我需要找到一种方法来消除将整个数据集复制到新表的过程，但是如何呢?

SELECT PostId, Message, (ROW_NUMBER() OVER (ORDER BY LastUpdateDateTime DESC)) as Row   
INTO #Results   
FROM   Posts
WHERE  Posts.TimePointId=@TimePointId AND IsFixed=0 
ORDER BY LastUpdateDateTime DESC
-----------------------------------------------------

SELECT * FROM #Results
WHERE Row BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results

在asp.net gridview中使用ROW_NUMBER()自定义分页

对于这样简单的操作，您真的不需要使用临时表，只需使用子查询或CTE即可。

使用子查询 

SELECT * 
FROM (
SELECT PostId, Message, (ROW_NUMBER() OVER (ORDER BY LastUpdateDateTime DESC)) as Row    
FROM   Posts
WHERE  Posts.TimePointId=@TimePointId AND IsFixed=0 
) Sub
WHERE Row BETWEEN(@PageIndex -1) * @PageSize + 1 
AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
使用CTE 

;WITH CTE
AS
 (
    SELECT PostId, Message, (ROW_NUMBER() OVER (ORDER BY LastUpdateDateTime DESC)) as Row     
    FROM   Posts
    WHERE  Posts.TimePointId=@TimePointId AND IsFixed=0 
 )
SELECT * 
FROM CTE
WHERE Row BETWEEN(@PageIndex -1) * @PageSize + 1 
AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

我不确定这是否会解决您的性能问题，您可以使用子查询而不是临时表:

SELECT p.*
FROM (SELECT PostId, Message, (ROW_NUMBER() OVER (ORDER BY LastUpdateDateTime DESC)) as Row    
      FROM   Posts
      WHERE  Posts.TimePointId=@TimePointId AND IsFixed=0 
     ) p
WHERE Row BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;

您可以使用CTE;

;With CTE as
(
SELECT PostId, Message, (ROW_NUMBER() OVER (ORDER BY LastUpdateDateTime DESC)) as Row   
FROM   Posts
WHERE  Posts.TimePointId=@TimePointId AND IsFixed=0 
ORDER BY LastUpdateDateTime DESC
)SELECT Row,* FROM CTE
WHERE Row BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
相关文章：