PostgreSQL中 FETCH FIRST ... WITH TIES 是查询结果中限制返回的行数

在 PostgreSQL 中，FETCH FIRST … WITH TIES 是一个在查询结果中限制返回的行数，但同时确保与最后一行具有相同排序值的所有行都被包括进来的子句。这通常与 ORDER BY 子句一起使用。

当您使用 FETCH FIRST n ROWS ONLY 时，您只会得到前 n 个排序后的行。但是，如果您希望包括与第 n 个行具有相同排序值的所有行（即“并列”的行），则可以使用 WITH TIES。

FETCH FIRST … WITH TIES 是 SQL 2008 标准的一部分，并且在 PostgreSQL 13 及更高版本中可用。如果您使用的是更早版本的 PostgreSQL，您将需要使用不同的方法来达到相同的效果，例如使用LIMIT … OFFSET … , 窗口函数或子查询。

以下是一个简单的示例来说明这一点：

假设我们有一个名为 t_employees 的表，其中包含员工的姓名和薪水：

CREATE TABLE t_employees (  
    nameinfo varchar(32),  
    salary numeric  
);  
  
INSERT INTO t_employees (nameinfo , salary) VALUES  
('user001', 50000),  
('devj001', 50000),  
('mscs001', 45000),  
('sqlp001', 45000),  
('gsql001', 40000);

pgdb01=# select * from t_employees;
 nameinfo | salary 
----------+--------
 user001  |  50000
 devj001  |  50000
 mscs001  |  45000
 sqlp001  |  45000
 gsql001  |  40000
(5 rows)

如果我们想找到薪水最高的前两个员工，但希望包括所有薪水一样名次并列的员工，我们可以这样做：

pgdb01=# select nameinfo,salary from t_employees ORDER BY salary DESC FETCH FIRST 1 ROWS WITH TIES;
 nameinfo | salary 
----------+--------
 user001  |  50000
 devj001  |  50000
(2 rows)

由于 user001 和 devj001 的薪水都是 50000（并列第一），且使用了WITH TIES，相同排序值的所有行都被包括结果中

pgdb01=# select nameinfo,salary from t_employees ORDER BY salary DESC FETCH FIRST 2 ROWS WITH TIES;
 nameinfo | salary 
----------+--------
 user001  |  50000
 devj001  |  50000
(2 rows)

pgdb01=# select nameinfo,salary from t_employees ORDER BY salary DESC FETCH FIRST 3 ROWS WITH TIES;
 nameinfo | salary 
----------+--------
 user001  |  50000
 devj001  |  50000
 mscs001  |  45000
 sqlp001  |  45000
(4 rows)

由于 user001 和 devj001 的薪水都是 50000（并列第一），并且 mscs001和 sqlp001的薪水都是 45000（并列第二），因此上述查询将返回 user001 、devj001 、mscs001和 sqlp001的信息。

如果使用LIMIT … OFFSET … 不会像 FETCH FIRST … WITH TIES 那样包含并列的行，出现间隙gap

pgdb01=# select nameinfo,salary from t_employees ORDER BY salary DESC LIMIT 3;
 nameinfo | salary 
----------+--------
 user001  |  50000
 devj001  |  50000
 mscs001  |  45000
(3 rows)

明显的sqlp001信息并返回在结果集中。此时gap就出现了，返回结果并非预期结果。

结合使用

虽然 FETCH FIRST … WITH TIES 和 LIMIT … OFFSET … 是不同的功能，但在某些情况下，您可能想要结合使用它们来达到特定的目的。但是，PostgreSQL 并不直接支持在一个查询中同时使用这两个子句。相反，您可能需要使用子查询或窗口函数来达到类似的效果。

例如，如果您想使用 WITH TIES 进行分页，您可能需要先确定每页的“边界”值（即每页最后一行的排序值），然后在外部查询中使用这个值来过滤结果。这通常涉及到更复杂的查询和逻辑。

总的来说，选择使用 FETCH FIRST … WITH TIES 还是 LIMIT … OFFSET … 取决于您的具体需求。如果您需要处理并列值并包括它们在内，那么 FETCH FIRST … WITH TIES 是更好的选择。如果您只是简单地需要限制返回的行数或进行分页，那么 LIMIT … OFFSET … 就足够了。