工作中遇到查完整目录的需求,如:图书>教辅>数学>高中数学
数据库中表结构是category( id,name,parent_id), parent_id存该目录的父目录,顶级目录的父目录id为-1
WITH RECURSIVE res AS (
SELECT
ID, NAME :: varchar(255), parent_id
FROM
ic_category
WHERE
id IN (1259828935862456324) --713703649615941632, 713703681362628608
UNION ALL
SELECT
t2.id, (t1.NAME || '>' || t2.NAME) :: varchar(255) AS NAME, t1.parent_id
FROM
ic_category t1
INNER JOIN res t2 on t1.ID = t2.parent_id
) SELECT
res.*
FROM
res;
结果: