赞
踩
PostgreSQL13中在select语句的fetch子句中新增了with ties选项,如:
Allow FETCH FIRST to use WITH TIES to return any additional rows that match the last result row
语法为:
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
具体介绍:
简单来说其用法就是:
使用with ties后将会把order by的字段的最后一行后面的数据也列出来,因此要注意该参数必须和order by子句一起使用。
例子:
–建表
bill=# create table t(c1 int,c2 int,c3 int);
CREATE TABLE
bill=# insert into t select 1,2,generate_series(1,5);
INSERT 0 5
bill=# insert into t select 1,2,generate_series(1,5);
INSERT 0 5
–fetch only
指定只获取2行。
bill=# select * from t order by c1,c2 offset 3 fetch first 2 row only;
c1 | c2 | c3
----+----+----
1 | 2 | 4
1 | 2 | 5
(2 rows)
–fetch with ties
将原先c1=1,c2=2后面的满足条件的数据也列举出来了。
bill=# select * from t order by c1,c2 offset 3 fetch first 2 row with ties;
c1 | c2 | c3
----+----+----
1 | 2 | 4
1 | 2 | 5
1 | 2 | 1
1 | 2 | 2
1 | 2 | 3
1 | 2 | 4
1 | 2 | 5
(7 rows)
–fetch with ties
需要注意的是,with ties列举的行和order by的列有关,例如这里我们order by的是c3列,前面的查询最后一行是c3=3,那么我们使用with ties也只能列出后面c3=3的行。
bill=# select * from t order by c3 offset 3 fetch first 2 row with ties;
c1 | c2 | c3
----+----+----
1 | 2 | 2
1 | 2 | 3
1 | 2 | 3
(3 rows)
最后再次提醒下,with ties只能和order by一起使用,否则会报错哦!
bill=# select * from t offset 3 fetch first 2 row with ties;
ERROR: WITH TIES options can not be specified without ORDER BY clause
参考链接:
https://www.postgresql.org/docs/13/release-13.html
https://www.postgresql.org/docs/13/sql-select.html
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。