深入探索MySQL——Limit查询限制

背景

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？
正如这一题：编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

刚看的时候就一阵酸爽。。。
当然，这道题涉及很多知识点，但是，我们以此为引，只讨论一下limit的使用。

导入

limit的使用格式：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

解读：LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
文中加粗那句话及其重要，这能解释为什么开篇那一题要求写“set N=N-1”；

mysql> SELECT * FROM table LIMIT 5,10;    // 检索记录行 6-15   
  
   //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
mysql> SELECT * FROM table LIMIT 95,-1;    // 检索记录行 96-last.   
  
   //如果只给定一个参数，它表示返回最大的记录行数目：    
mysql> SELECT * FROM table LIMIT 5;    //检索前 5 个记录行   

换句话说，LIMIT n 等价于 LIMIT 0,n。

比如，为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

再比如，如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

问题解答

我们回到开篇那一题上，可以看到，题目要求的N是个未知数，故而要想到mysql中的“存储过程和存储函数”：

CREATE FUNCTION getNthHighestSalary(N INT)
 RETURNS INT      //Begin前有return，是存储函数，其返回输出结果的类型
BEGIN
set N=N-1;      //注意：想想前面说的，limit第一个参数的含义
  RETURN (
      # Write your MySQL query statement below.
      select IFNULL((select distinct Salary from Employee order by Salary desc
                    limit N,1),NULL) as getNthHighestSalary
  );
END

问题扩展 - OffSet

前面说过，为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
offset经常用到在数据库中查询中间几条数据的需求
　　比如下面的sql语句：
　　　　① selete * from testtable limit 2,1;
　　　　② selete * from testtable limit 2 offset 1;
　　注意：
　　　　1.数据库数据计算是从0开始的——而offset存在的意义，就是打破这条规则！
　　　　2.offset X是跳过X个数据，limit Y是选取Y个数据
　　　　3.limit X,Y 中X表示跳过X个数据，读取Y个数据
　
　　这两个都是能完成需要，但是他们之间是有区别的：
　　　　①是从数据库中第三条开始查询，取一条数据，即第三条数据读取，一二条跳过
　　　　②是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。