PostgreSQL13新特性FETCH FIRST WITH TIES

在讲FETCH FIRST WITH TIES之前我们先来看下PG怎么分页？
语法一：
LIMIT { count | ALL }
OFFSET start

语法二：
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

postgres=# CREATE TABLE test(name varchar(64),score int2);
CREATE TABLE
postgres=# INSERT INTO test(name, score) VALUES('张三','100'),('李四','90'),('王五1','80'),('王五2','80'),('王五3','80');
INSERT 0 5
postgres=# SELECT * FROM test;
 name  | score 
-------+-------
 张三  |   100
 李四  |    90
 王五1 |    80
 王五2 |    80
 王五3 |    80
(5 rows)

--这个是我们常用的分页方式，LIMIT控制返回的行数，OFFSET控制起始行
postgres=# SELECT *
postgres-#   FROM test
postgres-#  ORDER BY score DESC
postgres-#  LIMIT 2 OFFSET 0;
 name | score 
------+-------
 张三 |   100
 李四 |    90
(2 rows)

--这个是SQL:2008中的语法，实现了一样的功能
--FIRST和NEXT等价，ROW和ROWS等价，OFFSET控制起始行，FETCH { FIRST | NEXT } 控制返回的行数
postgres=# SELECT *
postgres-#   FROM test
postgres-#  ORDER BY score DESC
postgres-#  OFFSET 0
postgres-#  FETCH FIRST 2 ROWS ONLY;
 name | score 
------+-------
 张三 |   100
 李四 |    90
(2 rows)
复制

如果SQL:2008中定义的语法和以前完全一样就没有必要新增语法了，其实只是PG实现不完全而已，在PG13又实现了一部分新功能，语法如下：
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

WITH TIES就是为了解决排名相同的问题，先看实例

--这里FETCH FIRST为3，按照以前的语法应该是返回3条数据，但是这里却返回了5条数据
--WITH TIES 选项表示返回排名和最后一行相同的额外数据行，而且必须同时指定 ORDER BY 子句
--因为第三行80分的有3条数据，所以就全部返回
--这里就解决了一页中的最后一行相同的数据在同一页展现的问题
postgres=# SELECT *
postgres-#   FROM test
postgres-#  ORDER BY score DESC
postgres-#  OFFSET 0
postgres-#  FETCH FIRST 3 ROWS WITH TIES;
 name  | score 
-------+-------
 张三  |   100
 李四  |    90
 王五1 |    80
 王五2 |    80
 王五3 |    80
(5 rows)

复制

在使用新语法新功能是也有一些注意事项：

--在使用WITH TIES 选项时必须指定 ORDER BY 子句，否则将会返回错误
postgres=# SELECT *
postgres-#   FROM test
postgres-#  FETCH FIRST 3 ROWS WITH TIES;
ERROR:  WITH TIES cannot be specified without ORDER BY clause

--分页中的起始行不再固定，因为前一页中的的最后一行有可能有排名相同的数据
postgres=# INSERT INTO test VALUES('王麻子','60');
INSERT 0 1
postgres=# SELECT * FROM test;
  name  | score 
--------+-------
 张三   |   100
 李四   |    90
 王五1  |    80
 王五2  |    80
 王五3  |    80
 王麻子 |    60
(6 rows)

--虽然指定了count为3，但实际返回了5行数据。如果我们基于这个功能实现分页查询，
--需要记录每次实际返回的行数；对应上面的示例，第一页返回5行数据，查询第二页时的语句如下:
postgres=# SELECT *
postgres-#   FROM test
postgres-#  ORDER BY score DESC
postgres-#  OFFSET 5
postgres-#  FETCH FIRST 3 ROWS WITH TIES;
  name  | score 
--------+-------
 王麻子 |    60
(1 row)

--WITH TIES是严格按照ORDER BY进行排序的，但是这里有个问题，例如我需要实现先按分数倒序排名，然后再按姓名进行排序，我们先把name的字段直接放入ORDER BY看看效果
postgres=# SELECT *
postgres-#   FROM test
postgres-#  ORDER BY score DESC,name
postgres-#  OFFSET 0
postgres-#  FETCH FIRST 3 ROWS WITH TIES;
 name  | score 
-------+-------
 张三  |   100
 李四  |    90
 王五1 |    80
(3 rows)
--以上结果并非我们想要的，我们需要的是第一页把为80分的全部返回，分数相同按姓名排序，
--但是这里少了数据，由于我们以上的写法排序是按照score和name排序，那么第三行并没有相同的排名了

--如果我们要实现以上需求可以使用通用表表达式或子查询
postgres=# SELECT * 
postgres-#   FROM (SELECT *
postgres(#           FROM test
postgres(#          ORDER BY score DESC
postgres(#         OFFSET 0
postgres(#          FETCH FIRST 3 ROWS WITH TIES) t 
postgres-#  ORDER BY score DESC,name;
 name  | score 
-------+-------
 张三  |   100
 李四  |    90
 王五1 |    80
 王五2 |    80
 王五3 |    80
(5 rows)

postgres=# WITH tmp_sort AS (SELECT *
postgres(#                     FROM test
postgres(#                    ORDER BY score DESC
postgres(#                   OFFSET 0
postgres(#                    FETCH FIRST 3 ROWS WITH TIES)
postgres-# SELECT * FROM tmp_sort ORDER BY score DESC,name;
 name  | score 
-------+-------
 张三  |   100
 李四  |    90
 王五1 |    80
 王五2 |    80
 王五3 |    80
(5 rows)

复制