排序和分页

ORDER BY 和 LIMIT 子句用于对查询结果进行排序和限制返回的行数。本章将详细介绍如何使用这些子句。

ORDER BY 排序

基本语法

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];

升序排序（默认）

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

-- 或明确指定 ASC
SELECT name, age
FROM users
ORDER BY age ASC;

降序排序

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

多列排序

-- 先按城市排序，再按年龄排序
SELECT name, city, age
FROM users
ORDER BY city ASC, age DESC;

结果示例：

name	city	age
赵六	北京	35
张三	北京	25
李四	上海	30
王五	广州	28

按表达式排序

-- 按计算结果排序
SELECT name, price, quantity, price * quantity AS total
FROM order_items
ORDER BY price * quantity DESC;

-- 按字符串长度排序
SELECT name
FROM users
ORDER BY LENGTH(name);

按列位置排序

-- 按 SELECT 列表中的位置排序
SELECT name, age, city
FROM users
ORDER BY 2 DESC;  -- 按第 2 列（age）排序

注意：不推荐使用列位置，因为不够清晰。

NULL 值排序

-- NULL 值默认排在最后（升序）或最前（降序）
SELECT name, email
FROM users
ORDER BY email;

-- 强制 NULL 值排在最前
SELECT name, email
FROM users
ORDER BY email IS NULL DESC, email;

-- 强制 NULL 值排在最后
SELECT name, email
FROM users
ORDER BY email IS NULL, email;

LIMIT 限制结果

基本语法

SELECT column1, column2
FROM table_name
LIMIT number;

限制返回行数

-- 只返回前 5 条记录
SELECT name, age
FROM users
LIMIT 5;

OFFSET 跳过行

-- 跳过前 10 条，返回接下来的 5 条
SELECT name, age
FROM users
LIMIT 5 OFFSET 10;

-- MySQL 简写语法
SELECT name, age
FROM users
LIMIT 10, 5;  -- LIMIT offset, count

分页查询

计算分页参数

-- 第 1 页（每页 10 条）
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

-- 第 2 页
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 10;

-- 第 3 页
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

分页公式

OFFSET = (page_number - 1) * page_size
LIMIT = page_size

完整分页示例

-- 获取第 3 页，每页 20 条
SET @page = 3;
SET @page_size = 20;
SET @offset = (@page - 1) * @page_size;

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT @page_size OFFSET @offset;

获取总记录数

-- 查询总记录数（用于计算总页数）
SELECT COUNT(*) AS total FROM users;

-- 计算总页数
SELECT CEIL(COUNT(*) / 20) AS total_pages FROM users;

TOP N 查询

查询前 N 条记录

-- 查询年龄最大的 5 个用户
SELECT name, age
FROM users
ORDER BY age DESC
LIMIT 5;

-- 查询销量最高的 10 个商品
SELECT name, sales_count
FROM products
ORDER BY sales_count DESC
LIMIT 10;

查询每组的前 N 条

-- 查询每个城市年龄最大的 3 个用户（MySQL 8.0+）
SELECT *
FROM (
    SELECT 
        name, city, age,
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) as rn
    FROM users
) t
WHERE rn <= 3;

随机排序

-- 随机获取 10 条记录
SELECT *
FROM users
ORDER BY RAND()
LIMIT 10;

注意：RAND() 在大表上性能较差。

实战示例

示例 1：商品列表分页

-- 商品列表，按销量排序，第 2 页
SELECT 
    id,
    name,
    price,
    sales_count
FROM products
WHERE is_active = TRUE
ORDER BY sales_count DESC, id ASC
LIMIT 20 OFFSET 20;

示例 2：最新订单

-- 获取最新的 10 个订单
SELECT 
    order_number,
    user_id,
    total_amount,
    created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

示例 3：排行榜

-- 用户消费排行榜前 20 名
SELECT 
    u.username,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC
LIMIT 20;

示例 4：分页优化

-- 使用 ID 范围分页（性能更好）
SELECT *
FROM users
WHERE id > 1000  -- 上一页的最后一个 ID
ORDER BY id
LIMIT 20;

性能优化

1. 为排序列创建索引

-- 为常用排序列创建索引
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_created_at ON orders(created_at);

2. 避免深度分页

-- 不推荐：深度分页性能差
SELECT * FROM users
ORDER BY id
LIMIT 20 OFFSET 100000;

-- 推荐：使用 WHERE 条件
SELECT * FROM users
WHERE id > 100000
ORDER BY id
LIMIT 20;

3. 覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_user_name_age ON users(name, age);

-- 查询只使用索引列
SELECT name, age
FROM users
ORDER BY name
LIMIT 10;

不同数据库的语法

MySQL

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

PostgreSQL

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

SQL Server

-- SQL Server 2012+
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

-- 旧版本
SELECT TOP 10 * FROM users
WHERE id NOT IN (
    SELECT TOP 20 id FROM users ORDER BY id
)
ORDER BY id;

Oracle

-- Oracle 12c+
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

-- 旧版本使用 ROWNUM
SELECT * FROM (
    SELECT u.*, ROWNUM rn FROM (
        SELECT * FROM users ORDER BY id
    ) u WHERE ROWNUM <= 30
) WHERE rn > 20;

常见错误

错误 1：ORDER BY 在 LIMIT 之后

-- 错误：语法错误
SELECT * FROM users
LIMIT 10
ORDER BY age;

-- 正确：ORDER BY 在 LIMIT 之前
SELECT * FROM users
ORDER BY age
LIMIT 10;

错误 2：忘记排序就分页

-- 不推荐：没有 ORDER BY 的分页结果不确定
SELECT * FROM users
LIMIT 10 OFFSET 20;

-- 推荐：始终使用 ORDER BY
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

最佳实践

1. 分页查询始终使用 ORDER BY
2. 为排序列创建索引
3. 避免深度分页
4. 使用稳定的排序列（如主键）
5. 考虑使用游标分页代替 OFFSET

小结

• ORDER BY：对结果排序
• ASC/DESC：升序/降序
• LIMIT：限制返回行数
• OFFSET：跳过指定行数
• 分页：LIMIT + OFFSET 实现分页
• 性能：创建索引，避免深度分页

下一步： 学习 GROUP BY 分组