PostgreSQL递归查询仅返回指向含照片叶子节点的层级路径

表结构与测试数据 #

category表（分类层级表） #

create table category (
  category_id integer, 
  category_name text, 
  parent_category integer, 
  level integer
);

insert into category values
(1, 'cat1', null, 1), 
(2, 'cat2', null, 1), 
(3, 'cat3', 1, 2), 
(4, 'cat4', 1, 2), 
(5, 'cat5', 1, 2), 
(6, 'cat6', 4, 3), 
(7, 'cat7', 4, 3),
(8, 'cat8', 3, 3),
(9, 'cat9', 3, 3),
(10, 'cat10', 5, 3),
(11, 'cat11', 5, 3);

photo表（关联照片的叶子节点表） #

create table photo(
  id integer,
  value text
);

insert into photo values
  (6,'photo'),
  (9,'photo'),
  (11,'photo');

当前问题 #

现有递归查询会返回全量分类节点，包含无关联照片的分支（如cat2、cat7等冗余节点）：

WITH RECURSIVE cte AS (
   SELECT category_id, category_name, parent_category, 1 AS level
   FROM   category
where level = 1
   UNION  ALL
   SELECT c.category_id, c.category_name, c.parent_category, ct.level + 1
   FROM   cte ct
   JOIN   category c ON c.parent_category = ct.category_id
   )
SELECT category_id,category_name,parent_category,value
FROM cte
  full join photo on cte.category_id = photo.id

当前返回结果中的冗余行（标记*）：

期望结果 #

仅返回指向含photo的叶子节点的完整层级路径，按路径自上而下排序：

解决方案 #

方法1：反向递归（轻量高效，无需额外扩展） #

从关联photo的叶子节点出发，向上递归获取所有父节点，仅保留需要的路径分支：

WITH RECURSIVE required_paths AS (
    -- 起始节点：有photo关联的分类
    SELECT c.category_id, c.category_name, c.parent_category, c.level
    FROM category c
    JOIN photo p ON c.category_id = p.id
    UNION ALL
    -- 向上递归获取父节点
    SELECT parent.category_id, parent.category_name, parent.parent_category, parent.level
    FROM required_paths child
    JOIN category parent ON child.parent_category = parent.category_id
)
SELECT rp.category_id, rp.category_name, rp.parent_category, p.value
FROM required_paths rp
LEFT JOIN photo p ON rp.category_id = p.id
-- 按层级升序、同层级分类ID排序，保证路径自上而下展示
ORDER BY rp.level, rp.category_id;

方法2：使用ltree+GIST索引（适合复杂层级场景） #

如果分类层级深、查询频繁，可借助PostgreSQL的ltree类型存储路径，配合GIST索引优化查询效率：

-- 先安装ltree扩展（首次使用时执行）
-- CREATE EXTENSION IF NOT EXISTS ltree;

WITH RECURSIVE category_paths AS (
    -- 生成根节点的路径
    SELECT category_id, category_name, parent_category, level, 
           category_name::ltree AS path
    FROM category
    WHERE parent_category IS NULL
    UNION ALL
    -- 递归生成子节点的完整路径
    SELECT c.category_id, c.category_name, c.parent_category, c.level,
           cp.path || c.category_name::ltree
    FROM category_paths cp
    JOIN category c ON cp.category_id = c.parent_category
),
-- 获取所有关联photo的节点路径
photo_paths AS (
    SELECT cp.path
    FROM category_paths cp
    JOIN photo p ON cp.category_id = p.id
)
SELECT cp.category_id, cp.category_name, cp.parent_category, p.value
FROM category_paths cp
LEFT JOIN photo p ON cp.category_id = p.id
-- 筛选属于photo节点路径前缀的所有节点
WHERE EXISTS (
    SELECT 1 FROM photo_paths pp
    WHERE pp.path @> cp.path
)
ORDER BY cp.level, cp.category_id;

若需优化查询速度，可给category表添加path字段并创建GIST索引：

ALTER TABLE category ADD COLUMN path ltree;
-- 初始化路径值（可通过递归语句批量生成）
CREATE INDEX idx_category_path ON category USING GIST (path);

内容的提问来源于stack exchange，提问作者Bastiaan Wakkie