递归查询在pgsql中的应用实例

递归查询（Recursive Query）在PostgreSQL（简称pgsql）中非常有用，尤其是在处理具有层次结构或递归关联的数据时。以下是一个应用实例，说明如何在pgsql中使用递归查询来获取所有子级类别（假设我们有一个类似于组织结构图的数据表）。

示例数据表结构

首先，假设我们有一个名为categories的表，它表示一个组织的类别层级：

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INTEGER REFERENCES categories(id) -- 指向父类别的ID，NULL表示顶级类别
);

插入示例数据

接下来，我们插入一些示例数据：

INSERT INTO categories (name, parent_id) VALUES ('Electronics', NULL);
INSERT INTO categories (name, parent_id) VALUES ('Computers', 1);
INSERT INTO categories (name, parent_id) VALUES ('Laptops', 2);
INSERT INTO categories (name, parent_id) VALUES ('Desktops', 2);
INSERT INTO categories (name, parent_id) VALUES ('Smartphones', 1);
INSERT INTO categories (name, parent_id) VALUES ('Cameras', 1);

使用递归查询获取所有子类别

现在，我们想要获取一个特定类别（例如’Computers’）的所有子类别。我们可以使用pgsql的递归公用表表达式（Recursive Common Table Expression，简称CTE）来实现这一点：

WITH RECURSIVE subcategories AS (
    -- 基础查询：选择顶级类别和它们的直接子类别
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询：选择当前类别的所有子类别
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN subcategories s ON c.parent_id = s.id
)
SELECT * FROM subcategories WHERE parent_id = (SELECT id FROM categories WHERE name = 'Computers');

这个查询首先使用WITH RECURSIVE关键字定义了一个递归CTE subcategories。基础查询选择了所有顶级类别（即parent_id为NULL的类别），然后递归查询通过连接categories表和subcategories表来选择每个类别的所有子类别。

最后，我们从subcategories表中选择所有子类别，其中parent_id等于’Computers’类别的ID。这将返回所有’Computers’类别的子类别，包括更深层次的子类别。