中文
devexamples

Offset Pagination Count Window

Implement classic offset pagination with a single-pass window count, page metadata, and sane caps on depth and page size.

SqlData Fetching

Code

snippet
-- 单次往返：数据页与总数用窗口函数一并取回
SELECT
  id,
  name,
  email,
  status,
  COUNT(*) OVER () AS total_matching
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 50;

-- 分页参数带硬上限（防恶意大页与深翻页）
-- 应用层生成的 SQL 遵循：
--   page_size <= 100
--   page_number * page_size <= 10000
SELECT id, name
FROM customers
ORDER BY id
LIMIT 100 OFFSET 0;

-- 跳转特定页（第 4 页，每页 25 条）
SELECT
  id, name,
  COUNT(*) OVER () AS total,
  (COUNT(*) OVER () + 24) / 25 AS total_pages
FROM customers
ORDER BY id
LIMIT 25 OFFSET 75;

Related Examples