当前位置：Gxlcms > 数据库问题 > SQL数据分页技术

SQL数据分页技术

时间：2021-07-01 10:21:17 帮助过：3人阅读

AdventureWorks2008

 

1、Top*Top

  1. <span style="color: #008080;"> 1</span> <span style="color: #808080;">-</span>使用 <span style="color: #0000ff;">Top</span><span style="color: #808080;">*</span><span style="color: #0000ff;">Top</span>
  2. <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@Start</span> <span style="color: #0000ff;">datetime</span>,<span style="color: #008000;">@end</span> <span style="color: #0000ff;">datetime</span><span style="color: #000000;">;
  3. </span><span style="color: #008080;"> 3</span> <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@Start</span><span style="color: #808080;">=</span><span style="color: #ff00ff;">getdate</span><span style="color: #000000;">();
  4. </span><span style="color: #008080;"> 4</span> 
  5. <span style="color: #008080;"> 5</span> <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@PageNumber</span> <span style="color: #0000ff;">INT</span>, <span style="color: #008000;">@PageSize</span> <span style="color: #0000ff;">INT</span>, <span style="color: #008000;">@Sql</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #ff00ff;">max</span><span style="color: #000000;">);
  6. </span><span style="color: #008080;"> 6</span> <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@PageNumber</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">5000</span><span style="color: #000000;">;
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@Pt</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">;
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@Sql</span><span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">SELECT T2.* FROM (
  9. </span><span style="color: #008080;"> 9</span> <span style="color: #ff0000;">    SELECT TOP 10 T1.* FROM 
  10. </span><span style="color: #008080;">10</span> <span style="color: #ff0000;">        (SELECT TOP </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">STR</span>(<span style="color: #008000;">@PageNumber</span><span style="color: #808080;">*</span><span style="color: #008000;">@PageSize</span>) <span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> * FROM Production.TransactionHistoryArchive
  11. </span><span style="color: #008080;">11</span> <span style="color: #ff0000;">        ORDER BY ReferenceOrderID ASC) AS T1
  12. </span><span style="color: #008080;">12</span> <span style="color: #ff0000;">    ORDER BY ReferenceOrderID DESC) AS T2
  13. </span><span style="color: #008080;">13</span> <span style="color: #ff0000;">ORDER BY ReferenceOrderID ASC</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  14. </span><span style="color: #008080;">14</span> <span style="color: #0000ff;">EXEC</span> (<span style="color: #008000;">@sql</span><span style="color: #000000;">);
  15. </span><span style="color: #008080;">15</span> 
  16. <span style="color: #008080;">16</span> <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@end</span><span style="color: #808080;">=</span><span style="color: #ff00ff;">getdate</span><span style="color: #000000;">();
  17. </span><span style="color: #008080;">17</span> <span style="color: #0000ff;">PRINT</span> <span style="color: #ff00ff;">Datediff</span>(millisecond,<span style="color: #008000;">@Start</span>,<span style="color: #008000;">@end</span>);

2、表变量

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

3、临时表

  1. <span style="color: #008080;">--</span><span style="color: #008080;">使用临时表</span>
  2. <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@Start</span> <span style="color: #0000ff;">datetime</span>,<span style="color: #008000;">@end</span> <span style="color: #0000ff;">datetime</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">SET</span> <span style="color: #008000;">@Start</span><span style="color: #808080;">=</span><span style="color: #ff00ff;">getdate</span><span style="color: #000000;">();
  4. </span><span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@PageNumber</span> <span style="color: #0000ff;">INT</span>, <span style="color: #008000;">@PageSize</span> <span style="color: #0000ff;">INT</span>, <span style="color: #008000;">@Sql</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #ff00ff;">max</span><span style="color: #000000;">);
  5. </span><span style="color: #0000ff;">SET</span> <span style="color: #008000;">@PageNumber</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">5000</span><span style="color: #000000;">;
  6. </span><span style="color: #0000ff;">SET</span> <span style="color: #008000;">@PageSize</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">;
  7. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> #local_variable(RowNumber <span style="color: #0000ff;">int</span> <span style="color: #ff00ff;">identity</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>),<span style="color: #ff0000;">[</span><span style="color: #ff0000;">TransactionID</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  8.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ProductID</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  9.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ReferenceOrderID</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  10.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ReferenceOrderLineID</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  11.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">TransactionDate</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">datetime</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  12.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">TransactionType</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">nchar</span><span style="color: #ff0000;">]</span>(<span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">),
  13.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Quantity</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  14.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ActualCost</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">money</span><span style="color: #ff0000;">]</span><span style="color: #000000;">,
  15.     </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">ModifiedDate</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">datetime</span><span style="color: #ff0000;">]</span><span style="color: #000000;">);
  16. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span><span style="color: #000000;"> #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
  17. </span><span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">50000</span> TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate <span style="color: #0000ff;">from</span> Production.TransactionHistoryArchive <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> ReferenceOrderID <span style="color: #0000ff;">ASC</span>
  18. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> #local_variable <span style="color: #0000ff;">where</span> RowNumber <span style="color: #808080;">></span> (<span style="color: #008000;">@PageNumber</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>)<span style="color: #808080;">*</span><span style="color: #008000;">@PageSize</span> <span style="color: #808080;">and</span> RowNumber <span style="color: #808080;"><=</span> <span style="color: #008000;">@PageNumber</span><span style="color: #808080;">*</span><span style="color: #008000;">@PageSize</span>
  19. <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@end</span><span style="color: #808080;">=</span><span style="color: #ff00ff;">getdate</span><span style="color: #000000;">();
  20. </span><span style="color: #0000ff;">PRINT</span> <span style="color: #ff00ff;">Datediff</span>(millisecond,<span style="color: #008000;">@Start</span>,<span style="color: #008000;">@end</span>);

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数据分页技术

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！