SQL数据库分页原理

发布网友 发布时间：2022-04-07 18:51

我来回答

共2个回答

懂视网 时间：2022-04-07 23:12

1、Top*Top

 1 -使用 Top*Top
 2 DECLARE @Start datetime,@end datetime;
 3 SET @Start=getdate();
 4 
 5 DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
 6 SET @PageNumber=5000;
 7 SET @Pt=10;
 8 SET @Sql=‘SELECT T2.* FROM (
 9  SELECT TOP 10 T1.* FROM 
10  (SELECT TOP ‘ + STR(@PageNumber*@PageSize) +‘ * FROM Production.TransactionHistoryArchive
11  ORDER BY ReferenceOrderID ASC) AS T1
12  ORDER BY ReferenceOrderID DESC) AS T2
13 ORDER BY ReferenceOrderID ASC‘;
14 EXEC (@sql);
15 
16 SET @end=getdate();
17 PRINT Datediff(millisecond,@Start,@end);

2、表变量

 1 --使用表变量
 2 DECLARE @Start datetime,@end datetime;
 3 SET @Start=getdate();
 4 DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
 5 SET @PageNumber=5000;
 6 SET @PageSize=10;
 7 
 8 DECLARE @local_variable table (RowNumber int identity(1,1),[TransactionID] [int],
 9 [ProductID] [int],
10 [ReferenceOrderID] [int],
11 [ReferenceOrderLineID] [int],
12 [TransactionDate] [datetime],
13 [TransactionType] [nchar](1),
14 [Quantity] [int],
15 [ActualCost] [money],
16 [ModifiedDate] [datetime]);
17 insert into @local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
18 SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
19 select * from @local_variable where RowNumber > (@PageNumber-1)*@PageSize and RowNumber <= @PageNumber*@PageSize
20 
21 SET @end=getdate();
22 PRINT Datediff(millisecond,@Start,@end);

3、临时表

--使用临时表
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();
DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @PageSize=10;

create table #local_variable(RowNumber int identity(1,1),[TransactionID] [int],
 [ProductID] [int],
 [ReferenceOrderID] [int],
 [ReferenceOrderLineID] [int],
 [TransactionDate] [datetime],
 [TransactionType] [nchar](1),
 [Quantity] [int],
 [ActualCost] [money],
 [ModifiedDate] [datetime]);
insert into #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
select * from #local_variable where RowNumber > (@PageNumber-1)*@PageSize and RowNumber <= @PageNumber*@PageSize

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);

4、ROW_NUMBER

--使用 ROW_NUMBER
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();

DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @PageSize=10;
SELECT * FROM
( SELECT ROW_NUMBER()
OVER(ORDER BY ReferenceOrderID) AS RowNumber,
*
FROM Production.TransactionHistoryArchive) AS T
WHERE T.RowNumber<=@PageNumber*@PageSize AND T.RowNumber>(@PageNumber-1)*@PageSize;

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);

SQL数据分页技术

标签：

热心网友 时间：2022-04-07 20:20