Oracle / MySQL / SQL Server分页查询公式整理

ゞ 浴缸里的玫瑰 2022-10-05 04:00 256阅读 0赞

/* Oracle */

  1. SELECT * FROM (
  2.     SELECT ROWNUM r, tab.* FROM tab_name tab
  3.         WHERE ROWNUM <= pageSize * pageNo) temp
  4. WHERE r > pageSize * pageNo - pageSize;
  6. SELECT *  FROM (
  7.     SELECT ROWNUM rn, temp.* FROM (
  8.         SELECT * FROM tab_name tab ORDER BY tab.pk_column DESC) temp
  9.         WHERE ROWNUM < end_num)
  10. WHERE rn > start_num;

/* MySQL */

  1. SELECT * FROM tab_name LIMIT(pageNo - 1) * pageSize, pageSize; (不推荐)
  3. SELECT * FROM tab_name WHERE pk_column >= (
  4.     SELECT pk_column FROM tab_name 
  5.     LIMIT (pageNo - 1) * pageSize, 1)
  6. LIMIT pageSize;
  8. SELECT * FROM tab_name AS main 
  9. JOIN (SELECT pk_column FROM tab_name LIMIT pageNo - 1 * pageSize, pageSize) AS sub 
  10. ON main.pk_column = sub.pk_column;

/* SQL Server */

  1. SELECT TOP pageSize * FROM (
  2.     SELECT ROW_NUMBER() OVER(
  3.         ORDER BY pk_column DESC) 
  4.                 AS rownumber, * FROM tab_name) temp
  5. WHERE rownumber > ((pageNo - 1) * pageSize);

Oracle参考：愚蠢的程序员 - oracle的分页查询
MySQL参考：scott_h - MySQL分页查询的性能优化
SQL Server参考：KANLON - SQL server分页的四种方法

发表评论

表情：
评论列表 （有 0 条评论，256人围观）

还没有评论，来说两句吧...

相关阅读