PostgreSQL递归查询|极客教程

PostgreSQL递归查询

在PostgreSQL中，递归查询是一种非常有用的功能，可以在数据中查找自身。递归查询常用于处理具有分层结构的数据，如组织结构、文件系统、评论/回复等情景。本文将介绍如何在PostgreSQL中使用递归查询来处理这些分层数据。

使用WITH RECURSIVE进行递归查询

在PostgreSQL中，我们使用WITH RECURSIVE关键字来执行递归查询。WITH RECURSIVE语句由两部分组成：非递归部分和递归部分。首先定义非递归部分，然后定义递归部分，最后合并两部分完成查询。

下面我们通过一个简单的示例来说明如何使用WITH RECURSIVE进行递归查询。假设我们有一个表geekdocs_comments用来存储网站文章的评论信息，其中每条评论记录包含id、content、parent_id字段，parent_id表示该评论所回复的评论的id。我们希望查询出所有评论的层级关系。

首先，创建一张示例表并插入一些数据：

CREATE TABLE geekdocs_comments (
    id serial PRIMARY KEY,
    content text,
    parent_id integer
);

INSERT INTO geekdocs_comments (content, parent_id) VALUES 
    ('comment 1', NULL),
    ('comment 2', 1),
    ('comment 3', 2),
    ('comment 4', 2),
    ('comment 5', 3),
    ('comment 6', 1),
    ('comment 7', 5);

接下来，使用WITH RECURSIVE进行递归查询：

WITH RECURSIVE cte_comments AS (
    SELECT id, content, parent_id, 1 AS level
    FROM geekdocs_comments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.content, c.parent_id, p.level + 1
    FROM geekdocs_comments c
    JOIN cte_comments p ON c.parent_id = p.id
)
SELECT id, space(level * 4) || content as content
FROM cte_comments
ORDER BY id;

运行上述查询后，我们可以得到如下结果：

 id |       content       
----+---------------------
  1 | comment 1
  2 |     comment 2
  6 |     comment 6
  3 |         comment 3
  5 |             comment 5
  7 |                 comment 7
  4 |         comment 4
(7 rows)

以上查询结果展示了每条评论的层级关系，comment 1为根评论，下面是其回复的评论。

使用递归查询处理组织结构

递归查询在处理组织结构时非常有用，可以方便的查询出某员工的所有下属员工。下面我们使用一个组织结构表geekdocs_employees来演示递归查询的用法。

首先，创建一张示例表并插入一些数据：

CREATE TABLE geekdocs_employees (
    id serial PRIMARY KEY,
    name text,
    manager_id integer
);

INSERT INTO geekdocs_employees (name, manager_id) VALUES 
    ('Alice', NULL),
    ('Bob', 1),
    ('Charlie', 1),
    ('David', 2),
    ('Eve', 3),
    ('Frank', 2),
    ('Grace', 4),
    ('Henry', 5);

接下来，使用WITH RECURSIVE进行递归查询：

WITH RECURSIVE cte_employees AS (
    SELECT id, name, manager_id
    FROM geekdocs_employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM geekdocs_employees e
    JOIN cte_employees p ON e.manager_id = p.id
)
SELECT id, name
FROM cte_employees
ORDER BY id;

运行上述查询后，我们可以得到如下结果：

 id |  name  
----+--------
  1 | Alice
  2 | Bob
  3 | Charlie
  4 | David
  5 | Eve
  6 | Frank
  7 | Grace
  8 | Henry
(8 rows)

以上查询结果展示了每个员工的层级关系，每个员工下面都是他的直接下属员工。

结语

递归查询是PostgreSQL中一个非常强大且实用的功能，可以处理许多分层数据结构的查询。

PostgreSQL递归查询