分页需求
正常的数据查询需求一般都需要进行分页操作，对于后端来说，不希望一次读取大量的数据，造成数据库的负担；对于前端来说，不希望一次展示过多数据，影响用户的交互体验。
PostgreSQL与MySQL分页的区别
- 如果从第一条开始取记录 , pg与mysql都支持 select * from tb limit A;
- 当从至少第二条数据取记录时 , pg 仅支持 limit A offset B , 而 mysql 除了支持 pg 的分页之外 , 还支持 limit B,A。
PostgreSQL 的分页
SELECT column1, column2, ...
FROM table
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[OFFSET m {ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows] { ROW | ROWS } ONLY];
SELECT column1, column2, ...
FROM table
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[LIMIT { num_rows| ALL } ]
[OFFSET m {ROW | ROWS}];
- FETCH子句与LIMIT子句作用相同，但 FETCH 是标准SQL语法，而LIMIT是PostgreSQL扩展语法。
- 不指定 ORDER BY，则结果集不进行排序。
- FIRST 和 NEXT 任选一个，两者含义相同。
- ROW 和ROWS 任选一个，两个含义相同。
- OFFSET 偏移量必须为 0 或者正整数。默认值为 0，NULL等同于 0。
- FETCH 限制数量必须为 0 或者正整数。默认值为 1，NULL等同于不限制数量。
- LIMIT 限制数量必须为 0 或者正整数。没有默认值，ALL 或者 NULL 等同于不限制数量。
- 随着 OFFSET 偏移量的增加，查询耗时线性增长，相当于先取出 OFFSET + LIMIT 的行之后，再把 OFFSET 的行去掉。
PostgreSQL 的 WITH TIES
给定一张成绩表scores
| id | name | score |
|---|---|---|
| 1 | alice | 90 |
| 2 | alex | 80 |
| 3 | xiaobai | 80 |
| 4 | xiaoming | 80 |
| 5 | daviad | 70 |
获取成绩最高的top 2
SELECT * FROM scores
ORDER BY score DESC
LIMIT 2;
SELECT * FROM scores
ORDER BY score DESC
FETCH FIRST 2 ROWS ONLY;
| id | name | score |
|---|---|---|
| 1 | alice | 90 |
| 2 | alex | 80 |
同等条件下获取所有重复值，使用 WITH TIES 子句。
SELECT * FROM scores
ORDER BY score DESC
FETCH FIRST 2 ROWS WITH TIES;
| id | name | score |
|---|---|---|
| 1 | alice | 90 |
| 2 | alex | 80 |
| 3 | xiaobai | 80 |
| 4 | xiaoming | 80 |
...
