postgres递归查询所有子部门
WITH RECURSIVE T ( ID, parent_id,depart_name ) AS ( SELECT A.ID, A.parent_id, a.depart_name FROM sys_depart A WHERE A.id = 'bcb9dc287fbe40178162a6f9c877f3ef' UNION ALL SELECT b.ID, b.parent_id , b.depart_name FROM sys_depart b, T WHERE b.parent_id = T.id ) SELECT ID, parent_id as "pId",depart_name as "text" FROM T
WITH RECURSIVE r AS (
SELECT * FROM test1 as t WHERE m_id = 18
union ALL
SELECT t.* FROM trade.merchant as t, r WHERE tree.par = r.m_id
)
SELECT * FROM r where stat = 1 ORDER BY m_id
它会一直递归调bai用,直到没有记录返回。
第一步,du会得到 SELECT * FROM test1 as t WHERE m_id = 18 ,一条记录。zhi并放在R中
然后再执行 SELECT t.* FROM trade.merchant as t, r WHERE tree.par = r.m_id 放入R中。 之后不断dao循环这一步。