关于常见数据库中SQL分页语法整理

千千梦丶琪  论坛元老 | 2024-10-31 08:15:31 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1735|帖子 1735|积分 5205

MySQL分页查询

在MySQL中，分页查询通常使用 LIMIT 关键字来实现。
LIMIT [offset,] rows 其中，offset表现偏移量（从结果集的第几行开始返回，默认从 0 开始计数），rows表现要返回的行数。
通例版

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

更灵活的分页查询
假如你需要更灵活地处置惩罚分页，可以使用变量来动态设置 offset 和 rows：
  1. SET @page_size := 10;
  2. SET @page_number := 2;
  3. SET @offset := (@page_size * (@page_number - 1));
  5. SELECT *
  6. FROM employees
  7. LIMIT @offset, @page_size;
  11. # 在存储过程中使用 LIMIT 进行分页查询，以下是一个简单的存储过程示例，用于实现分页查询：
  12.    DELIMITER $$
  13.    CREATE PROCEDURE get_page(IN page_number INT, IN page_size INT)
  14.    BEGIN
  15.        SET @offset = (page_number - 1) * page_size;
  16.        SET @limit = page_size;
  17.        SET @sql = CONCAT('SELECT * FROM your_table LIMIT ', @offset, ', ', @limit);
  18.        PREPARE stmt FROM @sql;
  19.        EXECUTE stmt;
  20.        DEALLOCATE PREPARE stmt;
  21.    END$$
  22.    DELIMITER ;
复制代码
使用 JOIN 或子查询举行分页
有时候，你可能需要在复杂的查询中举行分页，例如包罗 JOIN 或子查询：
  1. SELECT e.*
  2. FROM employees e
  3. JOIN (
  4.     SELECT employee_id
  5.     FROM employees
  6.     ORDER BY hire_date
  7.     LIMIT @offset, @page_size
  8. ) as sub_query ON e.employee_id = sub_query.employee_id;
复制代码
MySQL 8.0 引入了窗口函数，可以使用 ROW_NUMBER() 来实现分页：
  1. SELECT *
  2. FROM (
  3.     SELECT *, ROW_NUMBER() OVER (ORDER BY hire_date) as row_num
  4.     FROM employees
  5. ) as paginated_results
  6. WHERE row_num BETWEEN @offset + 1 AND @offset + @page_size;
复制代码
Oracle分页查询

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

在Oracle 12c之前的版本，分页查询通常依赖于ROWNUM伪列。ROWNUM为结果集中的每一行分配一个唯一的行号。
  1. SELECT * FROM (
  2.   SELECT temp.*, ROWNUM rnum FROM (
  3.     SELECT * FROM your_table WHERE conditions ORDER BY some_column
  4.   ) temp WHERE ROWNUM <= :upper_bound
  5. ) WHERE rnum > :lower_bound;
  6. # :upper_bound是上界值，通常是(页码 * 每页显示的行数)。
  7. # :lower_bound是下界值，通常是(页码 - 1) * 每页显示的行数 + 1。
  9. # 假设你有一个名为employees的表，你想查询第2页的数据，每页显示10行数据。
  10. SELECT * FROM (
  11.   SELECT temp.*, ROWNUM rnum FROM (
  12.     SELECT * FROM employees ORDER BY employee_id
  13.   ) temp WHERE ROWNUM <= 20
  14. ) WHERE rnum > 10;
复制代码

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

升级版

动态分页查询
有时候我们可能需要根据用户输入大概业务逻辑动态地确定每页显示的记录数和页码。
例如，假设我们有一个存储过程，担当两个参数：p_page_number（页码）和p_page_size（每页记录数）。
在这个存储过程中，首先计算偏移量v_offset，然后在内部子查询中使用分析函数ROW_NUMBER()对表中的数据按照some_column（根据实际需求替换为排序依据的列）举行排序和编号。接着在中层子查询中根据计算得到的偏移量和每页记录数限制ROWNUM，末了在外层子查询中根据rn（行号）筛选出真正需要的页面数据。
  1.    CREATE OR REPLACE PROCEDURE dynamic_paging(p_page_number IN NUMBER, p_page_size IN NUMBER) AS
  2.        v_offset NUMBER := (p_page_number - 1) * p_page_size;
  3.    BEGIN
  4.        -- 使用分析函数和ROWNUM进行分页
  5.        SELECT *
  6.        FROM (
  7.            SELECT t.*, ROWNUM rn
  8.            FROM (
  9.                SELECT col1, col2, col3, -- 这里列出实际表中的列
  10.                    -- 使用分析函数（例如ROW_NUMBER）对数据进行排序和编号
  11.                    ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
  12.                FROM your_table
  13.            ) t
  14.            WHERE ROWNUM <= v_offset + p_page_size
  15.        )
  16.        WHERE rn > v_offset;
  17.    END;
复制代码
多表连接与分页结合
当查询涉及多个表连接时，分页利用需要考虑连接结果的序次和数目。
假设有table1和table2两个表，我们要查询连接后的结果并举行分页。
这里先举行table1和table2的连接利用，然后使用ORDER BY对连接结果举行排序，接着在内部子查询中使用ROWNUM举行初步的分页筛选，末了在外层子查询中根据行号rn举行调解以得到精确的第一页数据。对于多表连接的分页查询，确保连接的精确性以及排序的合理性是很紧张的，这样才能得到精确的分页结果。
  1.    SELECT *
  2.    FROM (
  3.        SELECT t.*, ROWNUM rn
  4.        FROM (
  5.            SELECT col1, col2, col3 -- 这里列出连接后的列
  6.            FROM table1
  7.            JOIN table2 ON table1.key = table2.key
  8.            ORDER BY some_column
  9.        ) t
  10.        WHERE ROWNUM <= 10
  11.    )
  12.    WHERE rn > 0;
复制代码
分区表的分页优势
假如数据存储在分区表中，可以使用分区的特性更高效地举行分页查询。
假设我们有一个按照日期分区的表partitioned_table，要查询某个日期分区内的数据并举行分页。
通过指定分区名，可以直接在该分区内举行分页查询，减少了查询的数据量，提高了查询服从，尤其是在处置惩罚大型表时这种优势更加显着。
  1.    SELECT *
  2.    FROM (
  3.        SELECT t.*, ROWNUM rn
  4.        FROM (
  5.            SELECT col1, col2, col3
  6.            FROM partitioned_table PARTITION (partition_name) -- 替换为实际分区名
  7.            ORDER BY some_column
  8.        ) t
  9.        WHERE ROWNUM <= 10
  10.    )
  11.    WHERE rn > 0;
复制代码
PostgreSQL分页查询

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

使用 LIMIT 和 OFFSET 关键字
  1. # column_name是用于排序的列（如果不指定排序顺序，分页结果可能会不稳定），
  2. # count 是要返回的行数（即每页的行数），
  3. # offset_value是偏移量（表示从结果集的第几行开始返回）。
  4. SELECT * FROM table_name WHERE condition ORDER BY column_name
  5. LIMIT count OFFSET offset_value;
  8. # 查询第一页（假设每页显示 10 条记录）
  9. # 这里查询users表，按照user_id排序，返回前 10 条记录（偏移量为 0 表示从第一行开始）。
  10. SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 0;
  13. # 查询第二页
  14. SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 10;
复制代码
使用 FETCH 和 OFFSET（PostgreSQL 8.4 及以上版本支持 FETCH）
  1. SELECT * FROM your_table ORDER BY some_column OFFSET offset_value FETCH FIRST num_rows ROWS ONLY;
  3. SELECT * FROM users ORDER BY user_id OFFSET 0 FETCH FIRST 10 ROWS ONLY;
  5. SELECT * FROM users ORDER BY user_id OFFSET 10 FETCH FIRST 10 ROWS ONLY;
复制代码
升级版

更灵活的分页查询
  1. SET @page_size = 10;
  2. SET @page_number = 2;
  3. SET @offset = (@page_size * (@page_number - 1));
  5. SELECT *
  6. FROM employees
  7. ORDER BY employee_id
  8. LIMIT @page_size OFFSET @offset;
  11. # 在这个函数中，根据传入的页码p_page_number和每页大小p_page_size构建动态 SQL 语句。
  12. #然后使用RETURN QUERY EXECUTE执行动态 SQL 并返回结果。这种方式使得分页查询更加灵活，能够适应不同的需求。
  13. CREATE OR REPLACE FUNCTION dynamic_paging(p_page_number INT, p_page_size INT)
  14.     RETURNS SETOF your_table_type AS
  15. $BODY$
  16. DECLARE
  17.     v_sql TEXT;
  18. BEGIN
  19.     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);
  20.     RETURN QUERY EXECUTE v_sql;
  21. END;
  22. $BODY$
  23. LANGUAGE plpgsql;
复制代码
多表连接后的分页
  1. # 当查询涉及多个表的连接时，分页操作需要考虑连接结果集的顺序和结构。
  2. # 例如，假设有三个表table1、table2和table3，要对它们连接后的结果进行分页：
  3.    SELECT *
  4.    FROM (
  5.        SELECT sub.*, ROWNUM AS row_num
  6.        FROM (
  7.            SELECT t1.col1, t2.col2, t3.col3
  8.            FROM table1 t1
  9.                JOIN table2 t2 ON t1.key = t2.key
  10.                JOIN table3 t3 ON t2.other_key = t3.other_key
  11.            ORDER BY t1.some_column
  12.        ) sub
  13.    )
  14.    WHERE row_num BETWEEN start_row AND end_row;
  15. #首先在内部子查询中进行多表连接并按照table1中的some_column排序。
  16. #然后在中层子查询中使用ROWNUM（这里ROWNUM类似 Oracle 中的概念，
  17. #在 PostgreSQL 中是自定义的行号）为结果集编号。
  18. #最后在外层子查询中根据计算出的开始行start_row和结束行
  19. #end_row（可以根据页码和每页大小计算得出）进行分页筛选。
复制代码
使用窗口函数举行分页
PostgreSQL 8.4及以上版本支持窗口函数，可以使用 row_number() 窗口函数来实现更复杂的分页逻辑：
  1. WITH paginated AS (
  2.   SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
  3.   FROM employees
  4. )
  5. SELECT *
  6. FROM paginated
  7. WHERE rn BETWEEN 11 AND 20;
  10. #窗口函数可以在不改变结果集行数的情况下对每一行进行计算，这在复杂的分页场景中很有用。
  11. #例如，假设要对一个包含员工信息的表employees进行分页，并且在分页结果中显示每个员工在部门内的排名：
  12. SELECT department, employee_name, salary,
  13.        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
  14. FROM employees
  15. ORDER BY department, rank_in_department
  16. LIMIT page_size OFFSET offset;
  18. # 这里使用ROW_NUMBER()窗口函数计算每个员工在其所属部门内按照薪资降序排列的排名。
  19. # 然后按照部门和部门内排名进行排序，最后进行分页操作。这种方式可以在分页结果中提供更多的信息和分析价值。
复制代码
SQL Server分页查询

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

使用OFFSET - FETCH子句（SQL Server 2012 及以上版本）
  1. SELECT * FROM your_table ORDER BY some_column OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY;
  2. # 其中，your_table是要查询的表名，
  3. # some_column是用于排序的列（如果不指定排序顺序，分页结果可能会不稳定），
  4. # offset_rows是偏移量（表示从结果集的第几行开始返回），
  5. # fetch_rows是要返回的行数（即每页的行数）。
  7. # 查询第一页（假设每页显示 10 条记录）
  8. SELECT * FROM users ORDER BY user_id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
  9. #  查询第二页
  10. SELECT * FROM users ORDER BY user_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
复制代码
使用TOP关键字结合子查询（实用于旧版本）
  1. # 查询第一页（假设每页显示 10 条记录）
  2. SELECT TOP 10 * FROM your_table ORDER BY some_column;
  3. #  查询第二页（假设每页显示 10 条记录）
  4. # 这个方法相对复杂一些，在子查询中获取前 20 条记录，然后通过外部查询排除前 10 条记录来得到第二页的数据。
  5. SELECT * FROM (
  6.     SELECT TOP 20 * FROM your_table ORDER BY some_column
  7. ) AS subquery
  8. WHERE NOT EXISTS (SELECT TOP 10 * FROM your_table ORDER BY some_column)
  9. ORDER BY some_column;
复制代码
升级版

使用动态 SQL 实现灵活分页
  1. # 例如，创建一个存储过程，接受页码@pageNumber和每页行数@pageSize作为参数：
  2.    CREATE PROCEDURE DynamicPaging
  3.        @pageNumber INT,
  4.        @pageSize INT
  5.    AS
  6.    BEGIN
  7.        DECLARE @sql NVARCHAR(MAX);
  8.        SET @sql = N'SELECT * FROM (
  9.                    SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
  10.                    FROM your_table
  11.                ) AS subquery
  12.                WHERE row_num BETWEEN ' + CAST((@pageNumber - 1) * @pageSize + 1 AS NVARCHAR(10)) + ' AND ' + CAST(@pageNumber * @pageSize AS NVARCHAR(10));
  13.        EXEC sp_executesql @sql;
  14.    END;
  15. # 在这个存储过程中，首先构建动态 SQL 语句。使用ROW_NUMBER()函数为结果集中的每一行分配一个行号（按照some_column排序），
  16. # 然后根据传入的页码和每页行数计算出要获取的行号范围，最后通过sp_executesql执行动态 SQL 语句来实现分页查询。
复制代码
多表连接后的分页
  1. # 例如，假设有表table1和table2，要对它们连接后的结果进行分页：
  2.    SELECT *
  3.    FROM (
  4.        SELECT sub.*, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
  5.        FROM (
  6.            SELECT t1.col1, t2.col2
  7.            FROM table1 t1
  8.                JOIN table2 t2 ON t1.key = t2.key
  9.            ORDER BY some_column
  10.        ) AS sub
  11.    ) AS final
  12.    WHERE row_num BETWEEN start_row AND end_row;
  13. # 首先在内部子查询中进行table1和table2的连接，并按照some_column排序。
  14. #然后在中层子查询中使用ROW_NUMBER()函数为连接后的结果集分配行号。
  15. #最后在外层子查询中根据计算出的起始行start_row和结束行
  16. #end_row（可以根据页码和每页行数计算得出）进行分页筛选。
复制代码
与窗口函数结合的分页查询
  1. # 例如，要查询员工表employees中的数据并进行分页，同时显示每个部门内员工的排名：
  2.    SELECT department, employee_name, salary,
  3.           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
  4.    FROM employees
  5.    ORDER BY department, rank_in_department
  6.    OFFSET offset_rows ROWS FETCH NEXT fetch_rows ROWS ONLY;
  7. #这里使用ROW_NUMBER()窗口函数在每个部门内按照薪资降序为员工分配排名。
  8. #然后按照部门和部门内排名进行排序，最后再进行分页操作。这种方式可以在分页结果中提供更详细的部门内员工信息。
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

千千梦丶琪

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表