#排序和分页

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;

-- 按计算结果排序
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 值默认排在最后（升序）或最前（降序）
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;

SELECT column1, column2
FROM table_name
LIMIT number;

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

-- 跳过前 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;

-- 获取第 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;

-- 查询年龄最大的 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;

-- 查询每个城市年龄最大的 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;

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

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

-- 用户消费排行榜前 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;

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

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

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

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

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

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

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

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

-- 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 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;

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

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

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

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