OceanBase Cloud快速入门第41期：如何在查询中使用 LIMIT？

分布式数据库爱好者 2023-04-10

594

欢迎访问OceanBase官网获取更多信息：https://www.oceanbase.com/

LIMIT 可以限制 SELECT 查询返回的行数，常用于分页操作。

LIMIT 语法

查询中包含 LIMIT 的 SQL 语句格式如下：

SELECT select_list FROM table_list LIMIT [offset,] count_num;

参数说明如下：

offset 表示偏移量，即跳过多少行。offset 可以省略，默认为 0，表示跳过 0 行；offset 取值范围为 [0,+∞)。
count_num 表示跳过 offset 行之后开始获取数据，取 count_num 行记录；count_num 取值范围为 [0,+∞)。
offset 和 count_num 的值不能使用表达式。

使用 LIMIT n, m 查询

获取前 m 行记录

在 fruit_order 表中查询列 order_id ID、fruit_price 数据的前 5 条记录。

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order LIMIT 5;
+----+--------------+
| ID | 订单金额     |
+----+--------------+
|  1 |        13.11 |
|  2 |        23.34 |
|  3 |        12.22 |
|  4 |        22.21 |
|  5 |        51.55 |
+----+--------------+
5 rows in set

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order LIMIT 0,5;
+----+--------------+
| ID | 订单金额     |
+----+--------------+
|  1 |        13.11 |
|  2 |        23.34 |
|  3 |        12.22 |
|  4 |        22.21 |
|  5 |        51.55 |
+----+--------------+
5 rows in set

获取跳过 n 行记录后的 m 行记录

在表 fruit_order 查询第三行之后的三行数据。

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order LIMIT 3,3;
+----+--------------+
| ID | 订单金额     |
+----+--------------+
|  4 |        22.21 |
|  5 |        51.55 |
|  6 |        58.83 |
+----+--------------+
3 rows in set

当跳过 n 行记录后，剩余数据的行数小于 m 时，查询结果取所有剩余数据

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order;
+----+--------------+
| ID | 订单金额     |
+----+--------------+
|  1 |        13.11 |
|  2 |        23.34 |
|  3 |        12.22 |
|  4 |        22.21 |
|  5 |        51.55 |
|  6 |        58.83 |
|  7 |        14.66 |
|  8 |        34.44 |
|  9 |        63.66 |
+----+--------------+
9 rows in set

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order LIMIT 3,7;
+----+--------------+
| ID | 订单金额     |
+----+--------------+
|  4 |        22.21 |
|  5 |        51.55 |
|  6 |        58.83 |
|  7 |        14.66 |
|  8 |        34.44 |
|  9 |        63.66 |
+----+--------------+
6 rows in set

获取最大的一条记录

如果想获取订单金额最大的一条记录，可以先按照金额降序，然后取第一条记录。

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order ORDER BY fruit_price DESC LIMIT 1;
+----+--------------+
| ID | 订单金额      |
+----+--------------+
|  9 |        63.66 |
+----+--------------+
1 row in set

分页查询

LIMIT 也可以按照分页查询的逻辑进行使用。

SQL 语句格式如下：

SELECT select_list FROM table_list LIMIT (page_no - 1) * page_size, page_size;

语法的参数说明如下：

page_no 表示第几页，从 1 开始，范围为 [1,+∞)。
page_size 表示每页显示多少条记录，范围为 [1,+∞)。例如：page_no = 5，page_size = 10，表示获取第 5 页 10 条数据。

示例：

每页显示 2 条数据，依次获取第 1 页、第 2 页和第 3 页的数据。

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order ORDER BY fruit_price DESC;
+----+--------------+
| ID | 订单金额      |
+----+--------------+
|  9 |        63.66 |
|  6 |        58.83 |
|  5 |        51.55 |
|  8 |        34.44 |
|  2 |        23.34 |
|  4 |        22.21 |
|  7 |        14.66 |
|  1 |        13.11 |
|  3 |        12.22 |
+----+--------------+
9 rows in set

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order ORDER BY fruit_price DESC LIMIT 0,2;
+----+--------------+
| ID | 订单金额      |
+----+--------------+
|  9 |        63.66 |
|  6 |        58.83 |
+----+--------------+
2 rows in set

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order ORDER BY fruit_price DESC LIMIT 2,2;
+----+--------------+
| ID | 订单金额      |
+----+--------------+
|  5 |        51.55 |
|  8 |        34.44 |
+----+--------------+
2 rows in set

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order ORDER BY fruit_price DESC LIMIT 4,2;
+----+--------------+
| ID | 订单金额      |
+----+--------------+
|  2 |        23.34 |
|  4 |        22.21 |
+----+--------------+
2 rows in set

WHERE & GROUP BY & HAVING & ORDER BY & LIMIT 协作

当 WHERE、GROUP BY、HAVING、ORDER BY、LIMIT 这些关键字一起使用时，请遵照如下语法中指定的先后顺序：

SELECT select_list FROM table_name
WHERE query_condition  
GROUP BY group_by_expression  
HAVING group_condition 
ORDER BY column_list][ASC | DESC]
LIMIT [offset,] count_num;

示例如下：

obclient> SELECT user_id 客户ID, COUNT(order_id) 下单数量 FROM fruit_order t
      WHERE t.order_year = 2021
      GROUP BY user_id
      HAVING count(order_id)>=2
      ORDER BY 下单数量 DESC
      LIMIT 2;
+----------+--------------+
| 客户ID   | 下单数量     |
+----------+--------------+
|     1022 |            2 |
+----------+--------------+
1 row in set

注意事项

LIMIT 中不能使用表达式，只能跟随明确的数字。

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order LIMIT (3+1),7;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '(3+1),7' at line 1

LIMIT 后面的数字不能为负数。

obclient> SELECT order_id ID, fruit_price 订单金额 FROM fruit_order LIMIT -3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '-3' at line 1