SQL实现分页查询方法总结
<p>开发过程中经常遇到分页的需求，今天在此总结一下吧。</p>
<p>
简单说来方法有两种，一种在源上控制，一种在端上控制。源上控制把分页逻辑放在SQL层；端上控制一次性获取所有数据，把分页逻辑放在UI上（如GridView）。显然，端上控制开发难度低，适于小规模数据，但数据量增大时性能和IO消耗无法接受；源上控制在性能和开发难度上较为平衡，适应大多数业务场景；除此之外，还可以根据客观情况（性能要求，源与端的资源占用等）在源和端之间加一层，应用特殊算法和技术进行处理。以下主要讨论源上，即SQL上的分页。</p>
<p>
分页的问题其实就是在满足条件的一堆有序数据中截取当前所需要展示的那部分。实际上各种数据库都考虑到分页问题而内置了一些策略，比如MySql的LIMIT，Oracle的ROWNUM和ROW_NUMBER()，SqlServer的TOP和ROW_NUMBER()，基于此我们可以得到一系列分页的方法。</p>
<h3>
1、 基于MySql的LIMIT和Oracle的ROWNUM，可以直接限制返回区间（以MySql为例，注意使用Oracle的ROWNUM时要应用子查询）：</h3>
<h5>
方法一、直接限制返回区间</h5>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_769506">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql keyword">table</code> <code class="sql keyword">WHERE</code> <code class="sql plain">查询条件 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">排序条件 LIMIT ((页码-1)*页大小),页大小;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
优点：写法简单。</p>
<p>
缺点：当页码和页大小过大时，性能明显下降。</p>
<p>
适用：数据量不大。</p>
<h3>
2、基于LIMIT（MySql）、ROWNUM（Oracle）和TOP（SqlServer），他们可以限制返回的行数，因此可以得到以下两套通用的方法（以SqlServer为例）：</h3>
<h5>
方法二、NOT IN</h5>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_78186">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 * </code><code class="sql keyword">FROM</code> <code class="sql keyword">table</code> <code class="sql keyword">WHERE</code> <code class="sql plain">主键 </code><code class="sql color1">NOT</code> <code class="sql color1">IN</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">(页码-1)*页大小 主键 </code><code class="sql keyword">FROM</code> <code class="sql keyword">table</code> <code class="sql keyword">WHERE</code> <code class="sql plain">查询条件 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">排序条件</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">)</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">排序条件</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
优点：通用性强。</p>
<p>
缺点：当数据量较大时向后翻页，NOT IN中的数据过大会影响性能。</p>
<p>
适用：数据量不大。</p>
<h5>
方法三、MAX</h5>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_294802">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 * </code><code class="sql keyword">FROM</code> <code class="sql keyword">table</code> <code class="sql keyword">WHERE</code> <code class="sql plain">查询条件 </code><code class="sql color1">AND</code> <code class="sql plain">id ></code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql color2">ISNULL</code><code class="sql plain">(</code><code class="sql keyword">MAX</code><code class="sql plain">(id),0) </code><code class="sql keyword">FROM</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">(</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">((页码-1)*页大小) id </code><code class="sql keyword">FROM</code> <code class="sql keyword">table</code> <code class="sql keyword">WHERE</code> <code class="sql plain">查询条件 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">) </code><code class="sql keyword">AS</code> <code class="sql plain">tempTable</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">) </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
优点：速度快，特别是当id为主键时。</p>
<p>
缺点：适用面窄，要求排序条件单一且可比较。</p>
<p>
适用：简单排序（特殊情况也可尝试转换成类似可比较值处理）。</p>
<h3>
3、基于SqlServer和Oracle的ROW_NUMBER()，可以得到返回数据的行号，基于此在限制返回区间得到如下方法（以SqlServer为例）：</h3>
<h5>
方法四、ROW_NUMBER()</h5>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_272331">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">页大小 * </code><code class="sql keyword">FROM</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">(页码*页大小) ROW_NUMBER() OVER (</code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">排序条件) </code><code class="sql keyword">AS</code> <code class="sql plain">RowNum, * </code><code class="sql keyword">FROM</code> <code class="sql keyword">table</code> <code class="sql keyword">WHERE</code> <code class="sql plain">查询条件</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">) </code><code class="sql keyword">AS</code> <code class="sql plain">tempTable</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">RowNum </code><code class="sql color1">BETWEEN</code> <code class="sql plain">(页码-1)*页大小+1 </code><code class="sql color1">AND</code> <code class="sql plain">页码*页大小</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">RowNum</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
优点：在数据量较大时相比NOT IN有优势。</p>
<p>
缺点：小数据量时不如NOT IN。</p>
<p>
适用：大部分分页查询需求。</p>
<p>
到此这篇关于SQL实现分页查询方法总结的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。</p>
<p>
原文链接：https://www.cnblogs.com/bbgasj/archive/2012/11/06/2756567.html</p>
頁:
[1]