pqsql with recursive 到达某一个值就停止

在使用 PostgreSQL 数据库时，我们经常会遇到需要递归查询的场景。PostgreSQL 提供了 WITH RECURSIVE 关键字来处理这种情况，可以实现递归查询。本文将详细介绍 WITH RECURSIVE 的用法，并讨论如何在递归查询中设定终止条件。

什么是递归查询

递归查询是一种特殊的查询方式，它允许我们在查询语句中引用相同的表，并通过一个递归的过程来获取结果。递归查询在处理树状数据结构、组织结构、图表等场景非常有用。它可以帮助我们轻松地解决一些复杂的查询问题。

使用 WITH RECURSIVE 实现递归查询

在 PostgreSQL 中，我们可以使用 WITH RECURSIVE 关键字来实现递归查询。下面是 WITH RECURSIVE 的基本语法：

WITH RECURSIVE temp_table (column1, column2, ...) AS (
  SELECT initial_query
  UNION
  SELECT recursive_query
)
SELECT * FROM temp_table;

以上语法中的 temp_table 是一个临时表，我们可以在 SELECT 语句中引用这个表。initial_query 是初始查询语句，用来指定递归的起点。recursive_query 是递归查询语句，用来指定递归的规则。

为了更好地理解递归查询的工作原理，我们举一个简单的示例。假设我们有一个表 employees，其中包含员工的编号、姓名和直接上级的编号。我们希望通过递归查询找到某个员工的所有直接和间接下属。

首先，我们需要创建 employees 表并添加一些数据：

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES ('Alice', NULL);
INSERT INTO employees (name, manager_id) VALUES ('Bob', 1);
INSERT INTO employees (name, manager_id) VALUES ('Charlie', 1);
INSERT INTO employees (name, manager_id) VALUES ('Dave', 2);
INSERT INTO employees (name, manager_id) VALUES ('Eve', 3);

接下来，我们可以使用 WITH RECURSIVE 来查询某个员工的所有下属：

WITH RECURSIVE subordinates(emp_id, emp_name, manager_id, level) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE name = 'Alice'
  UNION
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT emp_name, level
FROM subordinates;

在上面的示例中，我们使用 subordinates 作为临时表的名称，它包含了 emp_id、emp_name、manager_id 和 level 这几个列。初始查询 SELECT id, name, manager_id, 0 FROM employees WHERE name = 'Alice' 返回了起点员工 “Alice” 的信息，并设置了初始的层级为 0。然后，递归查询 SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.emp_id 通过与临时表 subordinates 的连接，找到下属员工，同时将层级加一。最后，我们从临时表中选择了员工姓名和层级来展示结果。

设定递归查询的终止条件

使用 WITH RECURSIVE 进行递归查询时，我们通常需要设置一个终止条件来避免无限递归。在上面的示例中，我们没有设置终止条件，导致查询会一直进行下去。现在，我们将讨论如何设置终止条件。

在 WITH RECURSIVE 语句中，我们可以使用 UNION 和 WHERE 子句来设置终止条件。UNION 子句用于连接初始查询和递归查询的结果集，而 WHERE 子句用于筛选满足特定条件的行。通过使用 WHERE 子句，我们可以在递归过程中检查某个列的值是否满足我们的终止条件。

例如，我们想要递归查询员工下属的员工信息，直到层级达到 2 或者没有下属为止。可以按照以下方式设置终止条件：

WITH RECURSIVE subordinates(emp_id, emp_name, manager_id, level) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE name = 'Alice'
  UNION
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.emp_id
  WHERE s.level < 2 -- 设置终止条件
)
SELECT emp_name, level
FROM subordinates;

在递归查询的递归查询部分中，我们添加了 WHERE s.level < 2，表示递归查询只会进行两层，达到层级 2 就会终止。这样，我们可以避免无限递归，并且只查询到指定层级的结果。