PostgreSQL 13 新特性之 FETCH FIRST WITH TIES

文章目录

        测试数据
        PostgreSQL 12 之前的实现
        PostgreSQL 13 的增强功能
            注意事项
        总结

PostgreSQL 全球开发组于 2020 年 9 月 24 日宣布 PostgreSQL 13 正式发布。接下来我会通过一系列文章为大家解析这个新版本带来的功能增强，本文先给大家介绍一个与 SQL 开发相关的新特性：FETCH FIRST 子句增加了 WITH TIES 选项，可以用于返回更多排名相同的数据行。

测试数据

首先，我们创建一个简单的测试表 t 并插入一些数据：

CREATE TABLE t(name varchar(20), score int);
INSERT INTO t VALUES('张三', 80), ('李四', 77), ('王五', 80), ('赵六', 90), ('孙七', 80);

SELECT * FROM t;
name |score|
-----|-----|
张三  |   80|
李四  |   77|
王五  |   80|
赵六  |   90|
孙七  |   80|

该表中只包含姓名和分数 2 个字段，其中有 3 个学生的分数相同。
PostgreSQL 12 之前的实现

PostgreSQL 12 以及之前的版本实现了 SQL 标准中的 FETCH 子句：

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

其中，OFFSET 表示跳过指定的 start 行数， 默认为 0；FETCH 表示最多返回 count 行结果，默认为 1；ROW 和 ROWS 是同义词，FIRST 和 NEXT 是同义词；ONLY 表示不返回更多的数据。

例如，以下语句返回了分数最高的 2 个学生：

SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS ONLY;

name |score|
-----|-----|
赵六  |   90|
张三  |   80|

虽然的确返回了分数最高的两条记录，但是从表中的数据可以看出分数为 80 的还有两名同学，如果计算排名的话他们也应该是第二名。

另外需要注意，如果没有指定 ORDER BY 子句，查询将会返回随机的 2 行记录：

SELECT *
FROM t
FETCH FIRST 2 ROWS ONLY;

name |score|
-----|-----|
张三  |   80|
李四  |   77|

    📝PostgreSQL 还支持另一种限定查询结果数量的子句：LIMIT { count | ALL } OFFSET start，也可以实现相同的功能。

PostgreSQL 13 的增强功能

为了解决返回排名相同的数据问题，PostgreSQL 13 对 FETCH FIRST 子句进行了增强：

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

其中，WITH TIES 选项表示返回排名和最后一行相同的额外数据行，而且必须同时指定 ORDER BY 子句。例如，以下语句返回了分数排名前 2 的所有学生：

SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS WITH TIES;

name |score|
-----|-----|
赵六  |   90|
张三  |   80|
王五  |   80|
孙七  |   80|

如果使用 WITH TIES 选项时没有指定 ORDER BY 子句，将会返回错误：

SELECT *
FROM t
FETCH FIRST 2 ROWS WITH TIES;
RROR: WITH TIES cannot be specified without ORDER BY clause

注意事项
 

更多请见：http://www.mark-to-win.com/tutorial/51607.html