SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
带有 LIMIT 子句的 SELECT 语句的基本语法如下：
|
1 2 3 |
<span class="pln">SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> columnN FROM table_name LIMIT </span><span class="pun">[</span><span class="kwd">no</span><span class="pln"> of rows</span><span class="pun">]</span> |
下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：
|
1 2 3 |
<span class="pln">SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> columnN FROM table_name LIMIT </span><span class="pun">[</span><span class="kwd">no</span><span class="pln"> of rows</span><span class="pun">]</span><span class="pln"> OFFSET </span><span class="pun">[</span><span class="pln">row num</span><span class="pun">]</span> |
SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行，如下面的最后一个实例所示。
假设 COMPANY 表有以下记录：
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
下面是一个实例，它限制了您想要从表中提取的行数：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM COMPANY LIMIT </span><span class="lit">6</span><span class="pun">;</span> |
这将产生以下结果：
|
1 2 3 4 5 6 7 8 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> |
但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。下面是一个实例，从第三位开始提取 3 个记录：
|
1 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM COMPANY LIMIT </span><span class="lit">3</span><span class="pln"> OFFSET </span><span class="lit">2</span><span class="pun">;</span> |
这将产生以下结果：
|
1 2 3 4 5 6 7 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0 from:<a href="http://www.runoob.com/sqlite/sqlite-limit-clause.html">http://www.runoob.com/sqlite/sqlite-limit-clause.html</a></span> |