php的limt分页技巧_PG分页实现筹划总结limit演变出的五种实现筹划

pg官网wiki给了个count estimate(https://wiki.postgresql.org/wiki/Count_estimate)的办理方案,有兴趣的朋友也可往后面理解一下，下面先看一下PG的limit实现。

select from table limit A offset B;

参数：

A便是须要多少行；

B便是查询的出发点位置。

基于上面语法先容PG的5种分页实现方案，如下：

select from table where 自增列 in (select 自增列 from 表 ORDER BY 自增列 desc limit (select count() - (页码 显示数) from table)) ORDER BY 自增列 asc limit 显示数;

示例：

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

select from table where 自增列 not in (select 自增列 from table ORDER BY 自增列 asc limit 页码 显示数) ORDER BY 自增列 asc limit 显示数

示例：

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

select from table order by 排序列 asc LIMIT 显示数 offset (页码-1) 显示数

示例：

select from read_sight_order_log order by id asc LIMIT 10 offset 010

select from table where 自增列 BETWEEN 页码 显示数 and (1 + 页码) 显示数

示例：

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

select from (select ,row_number() over(order by 列 asc) as rownumber from table) t where t.rownumber > 页码显示数 and t.rownumber <= (1+页码)显示数

示例：

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、环境准备

$ psql -h 172.26.151.107 -p 5432 FSL-VIS postgres\dn --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分页实现

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;

以为有用的朋友多帮忙转发哦！
后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~