概述

前面介紹了Oracle、mysql、sqlserver的分頁實現(xiàn)方案了，所以今天主要介紹一下關于PG的分頁實現(xiàn)。傳統(tǒng)的精準分頁(能獲取到總頁數(shù)并且任意跳轉頁碼)在需要符合條件的記錄總數(shù)目,不過pg在這個地方是一個大坑,pg的count查詢性能不行(目測不如mysql,sqlserver,mongodb)。

pg官網wiki給了個count estimate(https://wiki.postgresql.org/wiki/Count_estimate)的解決方案,有興趣的朋友也可以后面了解一下，下面先看一下PG的limit實現(xiàn)。

---

PG limit 語法

?select * from table limit A offset B;

參數(shù)：

A就是需要多少行；

B就是查詢的起點位置。

基于上面語法介紹PG的5種分頁實現(xiàn)方案，如下：

---

1、方案1

select * from table where 自增列 in (select 自增列 from 表 ORDER BY 自增列 desc limit (select count(*) - (頁碼 * 顯示數(shù)) from table)) ORDER BY 自增列 asc limit 顯示數(shù);

示例：

select * from read_sight_order_log where id in ( select id from read_sight_order_log ORDER BY id desc limit (select count(*) - (0*10) from read_sight_order_log)) ORDER BY id asc limit 10

---

2、方案2

select * from table where 自增列 not in (select 自增列 from table ORDER BY 自增列 asc limit 頁碼 * 顯示數(shù)) ORDER BY 自增列 asc limit 顯示數(shù)

示例：

select * from read_sight_order_log where id not in (select id from read_sight_order_log ORDER BY id asc limit 0 * 10) ORDER BY id asc limit 10

---

3、方案3

?select * from table order by 排序列 asc LIMIT 顯示數(shù) offset (頁碼-1) * 顯示數(shù)

示例：

select * from read_sight_order_log order by id asc LIMIT 10 offset 0*10

---

4、方案4

select * from table where 自增列 BETWEEN 頁碼 * 顯示數(shù) and (1 + 頁碼) * 顯示數(shù)

示例：

select * from read_sight_order_log where id BETWEEN 0 * 10 and (1+0 )*10

---

5、方案5

select * from (select *,row_number() over(order by 列 asc) as rownumber from table) t where t.rownumber > 頁碼*顯示數(shù) and t.rownumber <= (1+頁碼)*顯示數(shù)

示例：

select * from (select *,row_number() over(order by id asc) as idx from read_sight_order_log)t where t.idx>0 * 10 and t.idx<= (1+0)*10

---

6、實例演示

6.1、環(huán)境準備

$ psql -h 172.26.151.107 -p 5432 FSL-VIS postgresdn --list schemasset search_path to 'fslvis_schema'; dt --list tablesCREATE TABLE t(EMPNO INT PRIMARY KEY NOT NULL,ENAME CHAR(10) NOT NULL,JOB CHAR(10) NOT NULL,MGR INT,HIREDATE date,SAL INT,COMM INT,DEPTNO INT);INSERT INTO t VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', NULL, '20');INSERT INTO t VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');INSERT INTO t VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');INSERT INTO t VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');INSERT INTO t VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');INSERT INTO t VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');INSERT INTO t VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');INSERT INTO t VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', NULL, '20');INSERT INTO t VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');INSERT INTO t VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');INSERT INTO t VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', NULL, '20');INSERT INTO t VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');INSERT INTO t VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');INSERT INTO t VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');

6.2、limit分頁實現(xiàn)

select * from t where empno in (select empno from t ORDER BY empno desc limit (select count(*) - (1 * 5) from t)) ORDER BY empno desc limit 5;--或者以下select * from t order by empno desc LIMIT 5 offset 0 * 5;

---

覺得有用的朋友多幫忙轉發(fā)哦！后面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~

總結

以上是生活随笔為你收集整理的sqlserver 分页_四类数据库分页实现方案总结之PG分页实现的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。