CoderPath

ORDER BY 与 LIMIT：排序与分页 ​

ORDER BY 基础 ​

默认情况下，SELECT 返回的行顺序是不确定的（取决于数据库内部存储）。使用 ORDER BY 可以指定排序规则。

基本语法：

sql
SELECT columns
FROM table
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
  • ASC：升序（从小到大），默认
  • DESC：降序（从大到小）

按单列排序 ​

sql
-- 按年龄升序排序
SELECT name, age FROM users ORDER BY age;

结果：

text
  name   | age
---------+-----
 Bob     |  25
 Diana   |  28
 Alice   |  30
 Charlie |  35
 Eve     |

注意：NULL 值默认排在最后（PostgreSQL）或最前（MySQL，取决于排序方向）。

sql
-- 按年龄降序排序
SELECT name, age FROM users ORDER BY age DESC;

结果：

text
  name   | age
---------+-----
 Eve     |     
 Charlie |  35
 Alice   |  30
 Diana   |  28
 Bob     |  25

控制 NULL 的位置 ​

PostgreSQL：

sql
-- NULL 排在最前
SELECT name, age FROM users ORDER BY age NULLS FIRST;

-- NULL 排在最后
SELECT name, age FROM users ORDER BY age NULLS LAST;

MySQL 没有 NULLS FIRST/LAST，需要用技巧：

sql
-- NULL 排在最后
SELECT name, age FROM users ORDER BY age IS NULL, age;

-- NULL 排在最前
SELECT name, age FROM users ORDER BY age IS NOT NULL, age;

按多列排序 ​

先按第一列排序，第一列相同的行再按第二列排序：

sql
-- 假设有相同年龄的用户
SELECT name, age FROM users ORDER BY age ASC, name ASC;

可以为每列指定不同的排序方向：

sql
SELECT name, age FROM users ORDER BY age DESC, name ASC;

这会先按年龄降序排序，年龄相同的按姓名升序排序。

实际示例：按创建时间和 ID 排序 ​

sql
SELECT id, name, created_at 
FROM users 
ORDER BY created_at DESC, id DESC;

这常用于显示"最新用户"列表，创建时间相同时按 ID 降序。

按表达式排序 ​

可以按计算结果排序：

sql
-- 按姓名长度排序
SELECT name, LENGTH(name) AS name_length 
FROM users 
ORDER BY LENGTH(name);

结果：

text
  name   | name_length
---------+-------------
 Bob     |           3
 Eve     |           3
 Alice   |           5
 Diana   |           5
 Charlie |           7

可以使用列别名（PostgreSQL 和 MySQL 都支持）：

sql
SELECT name, LENGTH(name) AS name_length 
FROM users 
ORDER BY name_length;

按 SELECT 中的列位置排序 ​

可以用列的位置编号（1, 2, 3...）代替列名：

sql
SELECT name, age FROM users ORDER BY 2 DESC;
-- 等价于 ORDER BY age DESC

不推荐使用：降低可读性，修改 SELECT 列表时容易出错。仅在动态 SQL 中偶尔使用。

LIMIT：限制返回行数 ​

LIMIT 用于限制查询返回的行数。

返回前 N 行 ​

sql
-- 返回年龄最大的 3 个用户
SELECT name, age 
FROM users 
WHERE age IS NOT NULL
ORDER BY age DESC 
LIMIT 3;

结果：

text
  name   | age
---------+-----
 Charlie |  35
 Alice   |  30
 Diana   |  28

注意：LIMIT 在 ORDER BY 之后执行。

执行顺序：FROM → WHERE → SELECT → ORDER BY → LIMIT

OFFSET：跳过前 N 行 ​

OFFSET 用于跳过前面的行，实现分页：

sql
-- 跳过前 2 个，返回接下来的 2 个
SELECT name, age 
FROM users 
WHERE age IS NOT NULL
ORDER BY age DESC 
LIMIT 2 OFFSET 2;

结果：

text
 name  | age
-------+-----
 Diana |  28
 Bob   |  25

分页实现 ​

分页公式：

sql
LIMIT page_size OFFSET (page_number - 1) * page_size

示例：每页 2 条记录

sql
-- 第 1 页
SELECT name, age FROM users WHERE age IS NOT NULL ORDER BY age DESC LIMIT 2 OFFSET 0;

-- 第 2 页
SELECT name, age FROM users WHERE age IS NOT NULL ORDER BY age DESC LIMIT 2 OFFSET 2;

-- 第 3 页
SELECT name, age FROM users WHERE age IS NOT NULL ORDER BY age DESC LIMIT 2 OFFSET 4;

MySQL 的简写语法 ​

MySQL 支持 LIMIT offset, count 语法：

sql
-- MySQL: LIMIT 2, 2 等价于 LIMIT 2 OFFSET 2
SELECT name, age FROM users ORDER BY age DESC LIMIT 2, 2;

PostgreSQL 不支持这种语法，建议统一使用 LIMIT ... OFFSET ...。

分页的性能问题 ​

OFFSET 性能随页码增大而下降：

sql
-- 第 1 页：快
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- 第 1000 页：慢，数据库需要扫描并跳过前 10000 行
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;

优化方法：游标分页（后续章节详细讨论）

sql
-- 记住上一页的最后一个 ID
SELECT * FROM users WHERE id > last_id ORDER BY id LIMIT 10;

SQL 标准差异 ​

LIMIT/OFFSET 的标准写法 ​

SQL 标准使用 FETCH 和 OFFSET（PostgreSQL 和 SQL Server 支持）：

sql
-- SQL 标准
SELECT name, age 
FROM users 
ORDER BY age DESC 
OFFSET 2 ROWS 
FETCH NEXT 2 ROWS ONLY;

PostgreSQL 同时支持 LIMIT 和 FETCH，推荐使用更简洁的 LIMIT。

MySQL 的 LIMIT ​

MySQL 只支持 LIMIT，不支持 FETCH。

没有 ORDER BY 的 LIMIT ​

如果没有 ORDER BY，LIMIT 返回的是"任意"的 N 行，每次执行可能不同：

sql
-- 危险：每次可能返回不同的 3 行
SELECT name FROM users LIMIT 3;

最佳实践：LIMIT 总是配合 ORDER BY 使用。

练习 ​

使用 users 表完成以下练习：

  1. 基本排序：查询所有用户，按年龄升序排序。

  2. 降序排序：查询所有用户，按年龄降序排序。

  3. 多列排序：按年龄降序排序，年龄相同的按姓名升序排序。

  4. 前 N 行：查询年龄最小的 2 个用户。

  5. 分页查询：实现"第 2 页"，每页 2 条记录，按年龄降序排序（跳过前 2 个）。

  6. 按表达式排序：查询所有用户，按姓名长度降序排序，长度相同的按姓名字母顺序排序。

  7. NULL 处理：查询所有用户按年龄升序排序，但 NULL 值排在最前面。

  8. 实际场景：创建一个查询，获取年龄大于 25 的用户，按创建时间降序排序，只返回前 3 个。

完成练习后，打开本章答案页核对。

常见错误 ​

错误 1：WHERE 中使用别名 ​

sql
-- 错误：WHERE 在 SELECT 之前执行
SELECT age AS user_age FROM users WHERE user_age > 30;

-- 正确
SELECT age AS user_age FROM users WHERE age > 30;

但是 ORDER BY 可以使用别名（因为它在 SELECT 之后执行）：

sql
-- 正确
SELECT age AS user_age FROM users ORDER BY user_age;

错误 2：没有 ORDER BY 就用 LIMIT ​

sql
-- 危险：每次可能返回不同结果
SELECT * FROM users LIMIT 5;

-- 正确：明确排序规则
SELECT * FROM users ORDER BY id LIMIT 5;

错误 3：分页时忘记一致的 ORDER BY ​

sql
-- 错误：不同页可能看到重复或遗漏数据
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT * FROM users LIMIT 10 OFFSET 10;

-- 正确：保持一致的排序
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

性能提示 ​

  1. 索引支持排序：在经常用于 ORDER BY 的列上创建索引（后续章节详细讨论）。

  2. 避免大 OFFSET：OFFSET 10000 需要扫描并跳过 10000 行，使用游标分页代替。

  3. 排序内存：大量数据排序需要内存或临时磁盘空间。

下一步 ​

学会了排序和分页。下一节学习 DISTINCT：如何去除重复行及其性能影响。

回到第一章概览 · 返回SQL 学习路线

用心记录每一步成长

Copyright © 2026-present Twiink