SQL Limit & Offset

1. 定義

慕課解釋： Limit用于對查詢結(jié)果集數(shù)量進(jìn)行限制；Offset 則常與 Limit 搭配，意為偏移，表示跳過一定數(shù)量的記錄。

2. 前言

本小節(jié)，我們將一起學(xué)習(xí) SQL 中的Limit和 Offset。

在實(shí)際的開發(fā)中，我們可能常常會遇到以下場景：

1. 獲得分?jǐn)?shù)的前 5 名。
2. 分頁獲取數(shù)據(jù)。

這些場景需要細(xì)粒度地控制數(shù)據(jù)庫數(shù)據(jù)，因此 SQL 引入了 Limit 和 Offset 來幫助開發(fā)者。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int,
  score int
);
INSERT INTO imooc_user(id,username,age,score)
VALUES (1,'peter', 18, 100),(2,'pedro', 24, 500),
(3,'jerry', 24, 500),(4,'mike', 18, 100),(5,'tom', 20, 1000);

提示： 理論上 Limit 一個(gè)指令就能實(shí)現(xiàn) Limit + Offset 的全部功能，但一些數(shù)據(jù)庫為了保持彼此的兼容性，仍然提供了Offset。

3. Limit

Limit 后面可接受一個(gè)參數(shù)或者兩個(gè)參數(shù)；當(dāng)接受一個(gè)參數(shù)count時(shí)，表示返回的結(jié)果集最多有 count 條。

語法如下：

SELECT [col] FROM [table_name] LIMIT [count];

其中col表示字段名，table_name表示數(shù)據(jù)表名稱，count代表限制數(shù)量。

3.1 例1 Limit 單參數(shù)使用

請書寫 SQL 語句，返回imooc_user表中年齡最大的3位用戶。

分析：

題干中指出只需返回 3 位用戶，因此需用到 Limit 限制數(shù)量；且年齡應(yīng)該為最大的 3 位，因此結(jié)果集應(yīng)按照年齡降序排序，即 ORDER BY age DESC。

語句：

整理可得語句如下：

SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 3;

結(jié)果如下：

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 24  |
| jerry    | 24  |
| tom      | 20  |
+----------+-----+

3.2 例2 Limit 雙參數(shù)使用

Limit 后面使用雙參數(shù)時(shí)，第一個(gè)參數(shù)表示偏移量，即 offset，表示從幾個(gè)記錄后開始返回，第二個(gè)參數(shù)表示最大數(shù)量，即 count，表示最多返回幾個(gè)記錄。我們合起來可以理解為，跳過一定數(shù)量（offset）的記錄，返回最多 count 個(gè)記錄。

語法如下：

SELECT [col] FROM [table_name] LIMIT [offset],[count];

其中col表示字段名，table_name表示數(shù)據(jù)表名稱，offset是偏移量，count是最大數(shù)量。

請書寫SQL語句，返回imooc_user表中年齡由大到小的第 2 位到第 4 位用戶。

分析：

題干中指出只需返回年齡的第 2 位到第 4 位用戶，因此需用到 Limit 限制數(shù)量和偏移；且年齡由大到小，因此結(jié)果集應(yīng)按照年齡降序排序，即 ORDER BY age DESC。

語句：

整理可得語句如下：

SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 1,3;

結(jié)果如下：

+----------+-----+
| username | age |
+----------+-----+
| jerry    | 24  |
| tom      | 20  |
| peter    | 18  |
+----------+-----+

第 2 位到第 4 位共 3 位用戶，因此 LIMIT 的第一個(gè)參數(shù)為 1，跳過第 1 個(gè)用戶，第二個(gè)參數(shù)為 3。

4. Offset

Offset 必須與 Limit 搭配使用，且二者都必須是單參數(shù)形式。Limit 和 Offset 搭配的作用等同于 Limit 使用雙參數(shù)。

語法如下：

SELECT [col] FROM [table_name] LIMIT [count] OFFSET [offset];

其中col表示字段名，table_name表示數(shù)據(jù)表名稱，offset是偏移量，count是最大數(shù)量。

4.1 例3 Limit 搭配 Offset 使用

請書寫 SQL 語句，返回imooc_user表中年齡由大到小的第 2 位到第 4 位用戶。

分析：

題干中指出只需返回年齡的第 2 位到第 4 位用戶，因此需用到 Limit 限制數(shù)量，Offset 來表示偏移；且年齡由大到小，因此結(jié)果集應(yīng)按照年齡降序排序，即 ORDER BY age DESC。

語句：

整理可得語句如下：

SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 3 OFFSET 1;

結(jié)果如下：

+----------+-----+
| username | age |
+----------+-----+
| jerry    | 24  |
| tom      | 20  |
| peter    | 18  |
+----------+-----+

第 2 位到第 4 位共 3 位用戶，因此 offset 應(yīng)是 1，跳過第 1 位用戶，count 應(yīng)是 3。

5. 小結(jié)

• Limit 和 Offset 常搭配用于數(shù)據(jù)分頁，是很多分頁工具的核心操作。
• Limit 其實(shí)完全可以替代 Limit + Offset，但考慮到SQL語句的可讀性，Limit + Offset的寫法更好。