#PostgreSQL LIMIT子句

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column]
LIMIT number;

-- 返回前10条记录
SELECT * FROM users LIMIT 10;

-- 返回前5个产品
SELECT name, price FROM products LIMIT 5;

-- 获取价格最高的5个产品
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

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

SELECT column1, column2, ...
FROM table_name
ORDER BY column
LIMIT number OFFSET skip;

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

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

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

-- 通用分页公式：OFFSET = (page - 1) * page_size

-- 等价于 LIMIT 10
SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

-- 等价于 LIMIT 10 OFFSET 5
SELECT * FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

-- 以下写法等价
FETCH FIRST 10 ROWS ONLY
FETCH NEXT 10 ROWS ONLY
FETCH FIRST 10 ROW ONLY
FETCH NEXT 10 ROW ONLY

-- 销售额最高的10个产品
SELECT p.name, SUM(oi.quantity * oi.unit_price) as total_sales
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY total_sales DESC
LIMIT 10;

-- 最活跃的5个用户
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY order_count DESC
LIMIT 5;

-- 假设每页20条，获取指定页
-- page: 页码（从1开始）
-- page_size: 每页条数

SELECT id, name, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET ((3 - 1) * 20);  -- 第3页

-- 同时获取总数用于分页信息
SELECT COUNT(*) FROM users;

-- 获取每个类别中价格最高的3个产品
SELECT * FROM (
    SELECT
        p.*,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY price DESC
        ) as rn
    FROM products p
) ranked
WHERE rn <= 3;

-- 以下两个查询等价
SELECT * FROM products LIMIT ALL;
SELECT * FROM products;

-- 大OFFSET值性能较差
SELECT * FROM logs ORDER BY id LIMIT 10 OFFSET 1000000;
-- 需要扫描前1000010行

-- 优化方案：使用游标或键集分页
SELECT * FROM logs
WHERE id > 1000000
ORDER BY id
LIMIT 10;

-- 不推荐：没有ORDER BY时结果不确定
SELECT * FROM users LIMIT 10;

-- 推荐：明确排序
SELECT * FROM users ORDER BY id LIMIT 10;

-- NULL值默认排在最后（ASC）或最前（DESC）
SELECT * FROM products ORDER BY price NULLS FIRST LIMIT 10;
SELECT * FROM products ORDER BY price NULLS LAST LIMIT 10;

SELECT column_list
FROM table_name
WHERE condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sorting_columns
LIMIT number
OFFSET skip;

-- 传统OFFSET分页（大数据量时慢）
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;

-- 键集分页（更快）
SELECT * FROM products
WHERE id > 10020  -- 上一页最后一条的ID
ORDER BY id
LIMIT 20;