数据库分页查询语句数据库查询

暖明人世间 發表於 2023-9-20 00:00:00

<p>
先看看单条 SQL 语句的分页 SQL 吧。</p>
<p>
方法1：</p>
<p>
适用于 SQL Server 2000/2005</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_415628">
<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 keyword">TOP</code> <code class="sql plain">页大小 * </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 </code><code class="sql keyword">WHERE</code> <code class="sql plain">id </code><code class="sql color1">NOT</code> <code class="sql color1">IN</code> <code class="sql plain">( </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 plain">table1 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id ) </code><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>
方法2：</p>
<p>
适用于 SQL Server 2000/2005</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_209486">
<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 keyword">TOP</code> <code class="sql plain">页大小 * </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 </code><code class="sql keyword">WHERE</code> <code class="sql plain">id > ( </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> <code class="sql plain">( </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 plain">table1 </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">id ) A ) </code><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>
方法3：</p>
<p>
适用于 SQL Server 2005</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_193964">
<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 keyword">TOP</code> <code class="sql plain">页大小 * </code><code class="sql keyword">FROM</code> <code class="sql plain">( </code><code class="sql keyword">SELECT</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">id) </code><code class="sql keyword">AS</code> <code class="sql plain">RowNumber,* </code><code class="sql keyword">FROM</code> <code class="sql plain">table1 ) A </code><code class="sql keyword">WHERE</code> <code class="sql plain">RowNumber > 页大小*(页数-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>
说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”以及“页大小*(页数-1)”替换成数码。</p>
<p>
MYSQL</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_131475">
<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>
</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 plain">TT LIMIT 1,20</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">TT LIMIT 21,30</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">/*</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">如果你是几千上万数据，就直接使用mysql自带的函数 limit的普通用法就ok了，如果是100万以上的数据，可能就要讲方法了，下面我们来做个百万级数据的分页查询语句.</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">news </code><code class="sql keyword">where</code> <code class="sql plain">id>=(</code><code class="sql keyword">select</code> <code class="sql plain">id </code><code class="sql keyword">from</code> <code class="sql plain">news limit 490000,1) limit 10; //0.18 sec //很明显,这种方式胜出 .</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">news limit 490000,10 //0.22 sec;</code>
</div>
<div class="line number7 index6 alt2">
<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>
以下的文章主要介绍的是MySQL分页的实际操作方案，其实关于实现MySQL分页的最简单的方法就是利用利用mysql数据库的LIMIT函数,LIMIT rows可以从MySQL数据库表中第M条记录开始检索N条记录的语句为：<br><br>
SELECT * FROM 表名称 LIMIT M,N<br>
例如从表Sys_option（主键为sys_id)中从第10条记录开始检索20条记录，语句如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_778780">
<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>
</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 plain">sys_option limit 10,20 </code>
</div>
<div class="line number2 index1 alt1">
<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 plain">[查询条件] </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">id limit ?,? </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
Oracle</p>
<p>
Oracle的分页查询语句基本上可以按照这篇了，下一篇文章会通过例子来申述。下面简单讨论一下多表联合的情况。对最多见的等值表连接查询，CBO 一般可能会采用两种连接方式NESTED LOOP以及HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个归回的最大记载数，NESTED LOOP在归回记载数跨越最大值时可以顿时遏制并将结果归回给中心层，而HASH JOIN必需处理完所有成集（MERGE JOIN也是）。那么在大部分的情况下，对分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问概率越小）。</p>
<p>
因此，如果不介意在体系中使用HINT的话，可以将分页的查询语句改写为：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_947824">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">/*+ FIRST_ROWS */ * </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 keyword">SELECT</code> <code class="sql plain">A.*, ROWNUM RN</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">TABLE_NAME) A</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">ROWNUM <= 40</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">)</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">RN >= 21</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
作者“ERDP技术架构”</p>

頁: [1]

琼殿技术论坛's Archiver

数据库分页查询语句数据库查询