Postgres рекурсивные запросы

April 1, 2015

Запрос снизу вверх. 8 и 3 - потомки

WITH RECURSIVE temp1 ( p_id, p_parent, p_title
) AS (
SELECT T1.p_id, T1.p_parent, T1.p_title
FROM "SC_Wonder".t_page T1
WHERE T1.p_id = 8 or T1.p_id = 3
union
select T2.p_id, T2.p_parent, T2.p_title
FROM "SC_Wonder".t_page T2
INNER JOIN temp1 ON (temp1.p_parent = T2.p_id))
select * from temp1 order by p_parent desc

..
Запрос сверху вниз. элемент с прдком 0 - корневой элемент

WITH RECURSIVE temp1 ( p_id,p_parent,p_title,PATH, LEVEL, NAME  ) AS (
SELECT T1.p_id,T1.p_parent, T1.p_title,
CAST (T1.p_id AS VARCHAR (50)) as PATH, 1 ,
CAST (T1.p_title AS VARCHAR (255)) as NAME
FROM "SC_Wonder".t_page T1 WHERE T1.p_parent = 0
union
select T2.p_id, T2.p_parent, T2.p_title,
CAST ( temp1.PATH ||'->'|| T2.p_id AS VARCHAR(50)) ,LEVEL + 1 ,
CAST ((repeat(' ', LEVEL+1)||T2.p_title) AS VARCHAR(255))
FROM "SC_Wonder".t_page T2 INNER JOIN temp1 ON( temp1.p_id= T2.p_parent))
select * from temp1 ORDER BY PATH LIMIT 100

..


Source: des1roer.blogspot.com

Комментарии

comments powered by Disqus