常见数据库中SQL分页语法整理大全(附示例)！

admin • • SQL技巧

常见数据库中SQL分页语法整理大全(附示例)！

作者：问题一箩筐
数据库分页是数据库管理系统中非常常见的一种操作,主要用于在大量数据中进行高效的浏览,提高用户体验,这篇文章主要介绍了常见数据库中SQL分页语法整理的相关资料,文中通过代码介绍的非常详细,需要的朋友可以参考下

MySQL分页查询

在MySQL中，分页查询通常使用 LIMIT 关键字来实现。

LIMIT [offset,] rows 其中，offset表示偏移量（从结果集的第几行开始返回，默认从 0 开始计数），rows表示要返回的行数。

常规版

1
2
3
4
5
6
7
# 查询第一页（假设每页显示 10 条记录）：
SELECT * FROM users LIMIT 0, 10;
#这里0是偏移量，表示从第 1 行（偏移量为 0）开始，10是要返回的行数。
#查询第二页：
SELECT * FROM users LIMIT 10, 10;
#此时偏移量为10，即跳过前面 10 行，然后返回 10 行。

升级版

更灵活的分页查询如果你需要更灵活地处理分页，可以使用变量来动态设置 offset 和 rows：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET @page_size := 10;
SET @page_number := 2;
SET @offset := (@page_size * (@page_number - 1));
SELECT *
FROM employees
LIMIT @offset, @page_size;
# 在存储过程中使用 LIMIT 进行分页查询，以下是一个简单的存储过程示例，用于实现分页查询：
DELIMITER $$
CREATE PROCEDURE get_page(IN page_number INT, IN page_size INT)
BEGIN
SET @offset = (page_number - 1) * page_size;
SET @limit = page_size;
SET @sql = CONCAT('SELECT * FROM your_table LIMIT ', @offset, ', ', @limit);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

使用 JOIN 或子查询进行分页有时候，你可能需要在复杂的查询中进行分页，例如包含 JOIN 或子查询：

1
2
3
4
5
6
7
8
SELECT e.*
FROM employees e
JOIN (
SELECT employee_id
FROM employees
ORDER BY hire_date
LIMIT @offset, @page_size
) as sub_query ON e.employee_id = sub_query.employee_id;

MySQL 8.0 引入了窗口函数，可以使用 ROW_NUMBER() 来实现分页：

1
2
3
4
5
6
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY hire_date) as row_num
FROM employees
) as paginated_results
WHERE row_num BETWEEN @offset + 1 AND @offset + @page_size;

Oracle分页查询

在Oracle数据库中，分页查询通常使用ROWNUM伪列或者数据库12c及更高版本中的FETCH FIRST和OFFSET子句来实现。

常规版

在Oracle 12c之前的版本，分页查询通常依赖于ROWNUM伪列。ROWNUM为结果集中的每一行分配一个唯一的行号。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM (
SELECT temp.*, ROWNUM rnum FROM (
SELECT * FROM your_table WHERE conditions ORDER BY some_column
) temp WHERE ROWNUM <= :upper_bound
) WHERE rnum > :lower_bound;
# :upper_bound是上界值，通常是(页码 * 每页显示的行数)。
# :lower_bound是下界值，通常是(页码 - 1) * 每页显示的行数 + 1。
# 假设你有一个名为employees的表，你想查询第2页的数据，每页显示10行数据。
SELECT * FROM (
SELECT temp.*, ROWNUM rnum FROM (
SELECT * FROM employees ORDER BY employee_id
) temp WHERE ROWNUM <= 20
) WHERE rnum > 10;

2025040709491032

Oracle 12c及更高版本使用OFFSET和FETCH进行分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT *
FROM your_table
WHERE conditions
ORDER BY some_column
OFFSET :lower_bound ROWS FETCH NEXT :page_size ROWS ONLY;
# :lower_bound是分页的起始行，通常是(页码 - 1) * 每页显示的行数。
# :page_size是每页显示的行数。
# 假设你有一个名为employees的表，你想查询第2页的数据，每页显示10行数据。
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
# 这里，OFFSET 10 ROWS跳过前10行，FETCH NEXT 10 ROWS ONLY则返回接下来的10行。

2025040709491133

升级版

动态分页查询有时候我们可能需要根据用户输入或者业务逻辑动态地确定每页显示的记录数和页码。

例如，假设我们有一个存储过程，接受两个参数：p_page_number（页码）和p_page_size（每页记录数）。

在这个存储过程中，首先计算偏移量v_offset，然后在内部子查询中使用分析函数ROW_NUMBER()对表中的数据按照some_column（根据实际需求替换为排序依据的列）进行排序和编号。接着在中层子查询中根据计算得到的偏移量和每页记录数限制ROWNUM，最后在外层子查询中根据rn（行号）筛选出真正需要的页面数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE PROCEDURE dynamic_paging(p_page_number IN NUMBER, p_page_size IN NUMBER) AS
v_offset NUMBER := (p_page_number - 1) * p_page_size;
BEGIN
-- 使用分析函数和ROWNUM进行分页
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT col1, col2, col3, -- 这里列出实际表中的列
-- 使用分析函数（例如ROW_NUMBER）对数据进行排序和编号
ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM your_table
) t
WHERE ROWNUM <= v_offset + p_page_size
)
WHERE rn > v_offset;
END;

多表连接与分页结合当查询涉及多个表连接时，分页操作需要考虑连接结果的顺序和数量。

假设有table1和table2两个表，我们要查询连接后的结果并进行分页。

这里先进行table1和table2的连接操作，然后使用ORDER BY对连接结果进行排序，接着在内部子查询中使用ROWNUM进行初步的分页筛选，最后在外层子查询中根据行号rn进行调整以得到正确的第一页数据。对于多表连接的分页查询，确保连接的正确性以及排序的合理性是很重要的，这样才能得到准确的分页结果。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT col1, col2, col3 -- 这里列出连接后的列
FROM table1
JOIN table2 ON table1.key = table2.key
ORDER BY some_column
) t
WHERE ROWNUM <= 10
)
WHERE rn > 0;

分区表的分页优势如果数据存储在分区表中，可以利用分区的特性更高效地进行分页查询。

假设我们有一个按照日期分区的表partitioned_table，要查询某个日期分区内的数据并进行分页。

通过指定分区名，可以直接在该分区内进行分页查询，减少了查询的数据量，提高了查询效率，尤其是在处理大型表时这种优势更加明显。

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT col1, col2, col3
FROM partitioned_table PARTITION (partition_name) -- 替换为实际分区名
ORDER BY some_column
) t
WHERE ROWNUM <= 10
)
WHERE rn > 0;

PostgreSQL分页查询

在PostgreSQL中，分页查询通常使用 LIMIT 和 OFFSET 子句来实现。这两个子句可以限制查询结果的数量，并指定返回结果的起始点。

常规版

使用 LIMIT 和 OFFSET 关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# column_name是用于排序的列（如果不指定排序顺序，分页结果可能会不稳定），
# count 是要返回的行数（即每页的行数），
# offset_value是偏移量（表示从结果集的第几行开始返回）。
SELECT * FROM table_name WHERE condition ORDER BY column_name
LIMIT count OFFSET offset_value;
# 查询第一页（假设每页显示 10 条记录）
# 这里查询users表，按照user_id排序，返回前 10 条记录（偏移量为 0 表示从第一行开始）。
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 0;
# 查询第二页
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 10;

使用 FETCH 和 OFFSET（PostgreSQL 8.4 及以上版本支持 FETCH）

1
2
3
4
5
SELECT * FROM your_table ORDER BY some_column OFFSET offset_value FETCH FIRST num_rows ROWS ONLY;
SELECT * FROM users ORDER BY user_id OFFSET 0 FETCH FIRST 10 ROWS ONLY;
SELECT * FROM users ORDER BY user_id OFFSET 10 FETCH FIRST 10 ROWS ONLY;

升级版

更灵活的分页查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SET @page_size = 10;
SET @page_number = 2;
SET @offset = (@page_size * (@page_number - 1));
SELECT *
FROM employees
ORDER BY employee_id
LIMIT @page_size OFFSET @offset;
# 在这个函数中，根据传入的页码p_page_number和每页大小p_page_size构建动态 SQL 语句。
#然后使用RETURN QUERY EXECUTE执行动态 SQL 并返回结果。这种方式使得分页查询更加灵活，能够适应不同的需求。
CREATE OR REPLACE FUNCTION dynamic_paging(p_page_number INT, p_page_size INT)
RETURNS SETOF your_table_type AS
$BODY$
DECLARE
v_sql TEXT;
BEGIN
v_sql := format('SELECT * FROM your_table ORDER BY some_column LIMIT %s OFFSET %s', p_page_size, (p_page_number - 1) * p_page_size);
RETURN QUERY EXECUTE v_sql;
END;
$BODY$
LANGUAGE plpgsql;

多表连接后的分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 当查询涉及多个表的连接时，分页操作需要考虑连接结果集的顺序和结构。
# 例如，假设有三个表table1、table2和table3，要对它们连接后的结果进行分页：
SELECT *
FROM (
SELECT sub.*, ROWNUM AS row_num
FROM (
SELECT t1.col1, t2.col2, t3.col3
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key
JOIN table3 t3 ON t2.other_key = t3.other_key
ORDER BY t1.some_column
) sub
)
WHERE row_num BETWEEN start_row AND end_row;
#首先在内部子查询中进行多表连接并按照table1中的some_column排序。
#然后在中层子查询中使用ROWNUM（这里ROWNUM类似 Oracle 中的概念，
#在 PostgreSQL 中是自定义的行号）为结果集编号。
#最后在外层子查询中根据计算出的开始行start_row和结束行
#end_row（可以根据页码和每页大小计算得出）进行分页筛选。

使用窗口函数进行分页PostgreSQL 8.4及以上版本支持窗口函数，可以使用 row_number() 窗口函数来实现更复杂的分页逻辑：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH paginated AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
FROM employees
)
SELECT *
FROM paginated
WHERE rn BETWEEN 11 AND 20;
#窗口函数可以在不改变结果集行数的情况下对每一行进行计算，这在复杂的分页场景中很有用。
#例如，假设要对一个包含员工信息的表employees进行分页，并且在分页结果中显示每个员工在部门内的排名：
SELECT department, employee_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees
ORDER BY department, rank_in_department
LIMIT page_size OFFSET offset;
# 这里使用ROW_NUMBER()窗口函数计算每个员工在其所属部门内按照薪资降序排列的排名。
# 然后按照部门和部门内排名进行排序，最后进行分页操作。这种方式可以在分页结果中提供更多的信息和分析价值。

SQL Server分页查询

在 SQL Server 中，分页查询可以通过使用 OFFSET 和 FETCH 子句来实现。这些子句在 SQL Server 2012 及更高版本中被引入，提供了一种简单且直观的方式来进行分页。

常规版

使用OFFSET - FETCH子句（SQL Server 2012 及以上版本）

1
2
3
4
5
6
7
8
9
10
SELECT * FROM your_table ORDER BY some_column OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY;
# 其中，your_table是要查询的表名，
# some_column是用于排序的列（如果不指定排序顺序，分页结果可能会不稳定），
# offset_rows是偏移量（表示从结果集的第几行开始返回），
# fetch_rows是要返回的行数（即每页的行数）。
# 查询第一页（假设每页显示 10 条记录）
SELECT * FROM users ORDER BY user_id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
#  查询第二页
SELECT * FROM users ORDER BY user_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

使用TOP关键字结合子查询（适用于旧版本）

1
2
3
4
5
6
7
8
9
# 查询第一页（假设每页显示 10 条记录）
SELECT TOP 10 * FROM your_table ORDER BY some_column;
#  查询第二页（假设每页显示 10 条记录）
# 这个方法相对复杂一些，在子查询中获取前 20 条记录，然后通过外部查询排除前 10 条记录来得到第二页的数据。
SELECT * FROM (
SELECT TOP 20 * FROM your_table ORDER BY some_column
) AS subquery
WHERE NOT EXISTS (SELECT TOP 10 * FROM your_table ORDER BY some_column)
ORDER BY some_column;

升级版

使用动态 SQL 实现灵活分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 例如，创建一个存储过程，接受页码@pageNumber和每页行数@pageSize作为参数：
CREATE PROCEDURE DynamicPaging
@pageNumber INT,
@pageSize INT
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM your_table
) AS subquery
WHERE row_num BETWEEN ' + CAST((@pageNumber - 1) * @pageSize + 1 AS NVARCHAR(10)) + ' AND ' + CAST(@pageNumber * @pageSize AS NVARCHAR(10));
EXEC sp_executesql @sql;
END;
# 在这个存储过程中，首先构建动态 SQL 语句。使用ROW_NUMBER()函数为结果集中的每一行分配一个行号（按照some_column排序），
# 然后根据传入的页码和每页行数计算出要获取的行号范围，最后通过sp_executesql执行动态 SQL 语句来实现分页查询。

多表连接后的分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 例如，假设有表table1和table2，要对它们连接后的结果进行分页：
SELECT *
FROM (
SELECT sub.*, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM (
SELECT t1.col1, t2.col2
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key
ORDER BY some_column
) AS sub
) AS final
WHERE row_num BETWEEN start_row AND end_row;
# 首先在内部子查询中进行table1和table2的连接，并按照some_column排序。
#然后在中层子查询中使用ROW_NUMBER()函数为连接后的结果集分配行号。
#最后在外层子查询中根据计算出的起始行start_row和结束行
#end_row（可以根据页码和每页行数计算得出）进行分页筛选。

与窗口函数结合的分页查询

1
2
3
4
5
6
7
8
# 例如，要查询员工表employees中的数据并进行分页，同时显示每个部门内员工的排名：
SELECT department, employee_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees
ORDER BY department, rank_in_department
OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY;
#这里使用ROW_NUMBER()窗口函数在每个部门内按照薪资降序为员工分配排名。
#然后按照部门和部门内排名进行排序，最后再进行分页操作。这种方式可以在分页结果中提供更详细的部门内员工信息。

总结

到此这篇关于常见数据库中SQL分页语法整理大全的文章就介绍到这了。

 

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>
相关内容